Extend and aggregate a pipeline

Summaries are easier to write when you can build the columns you need before you group. Pipe syntax lets you do that one step at a time.

How it works

EXTEND adds columns to every row and keeps the ones you already have. AGGREGATE changes the grain to one row per group and returns only the group keys and the aggregate results.

Common mistake

AGGREGATE changes the grain of the table. Columns that are not group keys are gone afterward.

Worked example

FROM customers
|> EXTEND state AS region
|> AGGREGATE COUNT(*) AS people GROUP BY region
|> ORDER BY people DESC

EXTEND adds region and keeps the customer rows.

AGGREGATE replaces those rows with one row per region and its count. The last step sorts the groups.

All BigQuery lessons