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.

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_make

The 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.

All BigQuery lessons