Skip to main content

Composing queries

Synced from semantic-layer

Source: docs/guide/11-composing.md at origin/main (3688d236), synced 2026-11-05. Do not edit this page here; change the source and run yarn sync:semantic.

A semantic select answers one question over one domain. To compare periods, rank, compute a share or join two questions, put each semantic select in a CTE and write ordinary SQL over the results. The layer computes each select on its own and runs the outer SQL over those results, all in one statement on one venue.

Period over period​

There is no compare keyword. Write two selects and join them:

WITH cur AS (
SELECT orders.region, MEASURE(revenue) AS rev
FROM commerce
WHERE time >= '2026-09-01' AND time < '2026-10-01'
GROUP BY ALL
),
prev AS (
SELECT orders.region, MEASURE(revenue) AS rev
FROM commerce
WHERE time >= '2026-08-01' AND time < '2026-09-01'
GROUP BY ALL
)
SELECT COALESCE(cur.region, prev.region) AS region,
cur.rev,
prev.rev AS prev_rev,
cur.rev / prev.rev - 1 AS growth,
cur.rev / SUM(cur.rev) OVER () AS share
FROM cur FULL OUTER JOIN prev ON cur.region = prev.region
ORDER BY growth DESC NULLS LAST
LIMIT 10

Every division is guarded: a zero divisor gives null on every warehouse, never an error or infinity.

Ranking and top-n​

WITH by_region AS (
SELECT orders.region, orders.channel, MEASURE(revenue) AS rev
FROM commerce
WHERE time >= :since AND time < :until
GROUP BY ALL
)
SELECT region, channel, rev
FROM by_region
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY rev DESC) = 1

For a plain top-n, a semantic select's own ORDER BY MEASURE(revenue) DESC LIMIT 10 is enough.

The rules for the outer SQL​

  • Every semantic select names the same domain, and a statement holds at most 8.
  • A semantic select has no subquery, no WITH of its own and no *. Compose over CTEs instead.
  • MEASURE and DATE_TRUNC(…, time) exist only inside a semantic select. Outside one, read the column you aliased.
  • Every relation is the domain or a CTE the statement defines. No warehouse table, file reader or table function can be named. A CTE cannot take the domain's name, a dataset's name, or the __sl_ prefix.
  • An unnamed outer column is output as column_<n>.
  • A LIMIT with no ORDER BY gets an ordering over every column, so the same rows come back every time.

Functions you may use​

The outer SQL admits only functions that mean the same on every warehouse:

KindFunctions
AggregateSUM, AVG, MIN, MAX, COUNT
Window and rankingROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, NTILE, PERCENT_RANK, CUME_DIST
NumericABS, ROUND, FLOOR, CEIL, SQRT, LN, EXP, POW, SIGN
General and stringCOALESCE, NULLIF, UPPER, LOWER, LENGTH, TRIM, SUBSTRING, CONCAT, REPLACE, LEFT, RIGHT

And those it refuses, with what to write instead:

RefusedWrite instead
LAST_VALUEFIRST_VALUE(x) OVER (… ORDER BY … DESC)
GREATEST, LEASTCASE WHEN a >= b THEN a ELSE b END
MEDIAN, PERCENTILE_CONT, QUANTILE and the likea median or percentile metric, through MEASURE()
EXTRACT, AT TIME ZONEDATE_TRUNC(grain, time) in the semantic select
FILTER (WHERE …)SUM(CASE WHEN … THEN … END)
OFFSET, FETCHLIMIT n. There is no paging.
PIVOT, ROLLUP, CUBE, GROUPING SETSconditional aggregates, or the totals as a second select
UNNEST, LATERAL, VALUESmodel the data as a dataset
COUNT(DISTINCT a, b)count the distinct pairs in a CTE
TRY_CASTCAST, to a plain scalar type
DISTINCT ONQUALIFY ROW_NUMBER() OVER (…) = 1

Anything not on the first list is refused (SQL_NOT_ALLOWED, naming the construct).

Adding up a metric that does not add up​

Only a metric whose rollup is sum can be re-totalled by summing its rows (Metrics). That rules out a ratio, an average, a distinct count, a median or a percentile: summing daily conversion rates does not give the month's rate. It also rules out a derived metric that adds two sums, such as revenue + fees, whose rollup is components: a group with no fees rows reads null there, and summing would drop that group's revenue. So SUM or AVG over any such column, however many CTEs away, is refused. The error text names ratios and averages even when the column is a sum of sums:

-- refused: NON_ADDITIVE_REAGGREGATION
WITH daily AS (
SELECT DATE_TRUNC('day', time) AS day, MEASURE(aov) AS aov
FROM commerce
WHERE time >= :since AND time < :until
GROUP BY ALL
)
SELECT SUM(aov) FROM daily

Ask for the metric at the coarser grouping in a second select instead. Or, when the metric's rollup is components, sum its components and apply its expr yourself (Metrics).

A share of a total is the same problem. s / SUM(s) OVER () is refused for any such metric, so ask for the total in its own select and divide:

WITH by_region AS (
SELECT orders.region, MEASURE(aov) AS aov
FROM commerce
WHERE time >= :since AND time < :until
GROUP BY ALL
),
overall AS (
SELECT MEASURE(aov) AS aov
FROM commerce
WHERE time >= :since AND time < :until
)
SELECT by_region.region, by_region.aov, by_region.aov / overall.aov AS vs_overall
FROM by_region CROSS JOIN overall