Join and window pipeline stages
Real questions often need columns from two tables, or a comparison against a total. Both fit into a pipeline without nested queries.
How it works
A join brings matching rows and columns from two inputs together. A window expression then looks at a set of the joined rows, and EXTEND adds its result without changing the number of rows.
|> INNER JOIN right USING (key)keeps only rows whose keys match and returns the shared key once.- Window functions use
OVER (...).OVER ()covers the whole current table. |> EXTEND window_expression AS nameadds the analytic value to every current row.
Common mistake
If a key repeats, the join produces duplicate rows. A window function such as AVG then counts every duplicate, so the duplicates weight the result.
Worked example
(SELECT 7 AS id, 'Ada' AS name UNION ALL SELECT 8, 'Lin')
|> INNER JOIN (SELECT 7 AS id, 40 AS score UNION ALL SELECT 8, 60) USING (id)
|> EXTEND AVG(score) OVER () AS average_score
|> ORDER BY idUSING matches the two id values and keeps a single id column next to name and score.
AVG looks at both joined rows, so average_score is 50 on each output row.