Composing queries
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
WITHof its own and no*. Compose over CTEs instead. MEASUREandDATE_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
LIMITwith noORDER BYgets 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:
| Kind | Functions |
|---|---|
| Aggregate | SUM, AVG, MIN, MAX, COUNT |
| Window and ranking | ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, NTILE, PERCENT_RANK, CUME_DIST |
| Numeric | ABS, ROUND, FLOOR, CEIL, SQRT, LN, EXP, POW, SIGN |
| General and string | COALESCE, NULLIF, UPPER, LOWER, LENGTH, TRIM, SUBSTRING, CONCAT, REPLACE, LEFT, RIGHT |
And those it refuses, with what to write instead:
| Refused | Write instead |
|---|---|
LAST_VALUE | FIRST_VALUE(x) OVER (… ORDER BY … DESC) |
GREATEST, LEAST | CASE WHEN a >= b THEN a ELSE b END |
MEDIAN, PERCENTILE_CONT, QUANTILE and the like | a median or percentile metric, through MEASURE() |
EXTRACT, AT TIME ZONE | DATE_TRUNC(grain, time) in the semantic select |
FILTER (WHERE …) | SUM(CASE WHEN … THEN … END) |
OFFSET, FETCH | LIMIT n. There is no paging. |
PIVOT, ROLLUP, CUBE, GROUPING SETS | conditional aggregates, or the totals as a second select |
UNNEST, LATERAL, VALUES | model the data as a dataset |
COUNT(DISTINCT a, b) | count the distinct pairs in a CTE |
TRY_CAST | CAST, to a plain scalar type |
DISTINCT ON | QUALIFY 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