Name a step with WITH
A long query is hard to read and hard to fix. A common table expression, or CTE, splits it into named steps you can check one at a time.
How it works
WITH name AS (query) names a query for the one statement that follows it. Read it from the top: each step is a small table, and the last SELECT gives the result.
- Write
WITH name AS (SELECT ...), then the finalSELECT, which readsnamein itsFROM. - For several steps write
WITHonce and separate them with commas:WITH a AS (...), b AS (...) SELECT .... - A step can read the steps above it. It cannot read a step below it, because BigQuery then looks for a real table with that name.
- A CTE lasts for one statement. The next statement in the editor cannot read it.
Common mistake
A CTE is a name for a query and holds no saved result. BigQuery runs it again for each place that reads it, so a step with RAND() that you read twice can give two different answers.
Worked example
WITH per_make AS (
SELECT make, COUNT(*) AS tests
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY make
)
SELECT COUNT(*) AS makes, MAX(tests) AS most_tests
FROM per_makeThe step per_make counts the tests of each make in March 2024.
The final SELECT reads those counts as if they were a table, and turns them into one row.