BigQuery Common Table Expressions: 10 Code-Along Examples

The CTE guide’s promise is simple: WITH turns inside-out queries into top-to-bottom pipelines you can read like a recipe. This workbook makes that transformation concrete. You will write your first CTE, refactor a nested subquery, chain steps, reference one CTE twice, build the classic top-customers pipeline, solve top-N per group, and finish by comparing CTEs with temp tables and views so you know when each belongs. Run Example 1 first; the rest depend on it.

1. Create the sample tables

Run this once. Three small tables with tiny numbers so you can verify every result by eye. Purchases carry a status and a date, which the later examples use.

-- Run once. Change `scratch` to any dataset you can write to.
CREATE OR REPLACE TABLE scratch.customers AS
SELECT * FROM UNNEST([
  STRUCT(1 AS customer_id, 'Ava'   AS name, 'UK' AS country),
  STRUCT(2, 'Ben',   'UK'),
  STRUCT(3, 'Chloe', 'US'),
  STRUCT(4, 'Dan',   'US')          -- never makes a purchase
]);
CREATE OR REPLACE TABLE scratch.products AS
SELECT * FROM UNNEST([
  STRUCT(101 AS product_id, 'Keyboard' AS name, 'Accessories' AS category, 80 AS price),
  STRUCT(102, 'Monitor', 'Displays', 300),
  STRUCT(103, 'Mouse', 'Accessories', 25),
  STRUCT(104, 'Webcam', 'Accessories', 60)
]);
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 1 AS customer_id, 102 AS product_id, 300 AS amount, 'completed' AS status, DATE '2024-05-01' AS purchase_date),
  STRUCT(2, 1, 101, 80,  'completed', DATE '2024-05-03'),
  STRUCT(3, 1, 103, 50,  'refunded',  DATE '2024-06-01'),
  STRUCT(4, 2, 103, 25,  'completed', DATE '2024-05-10'),
  STRUCT(5, 3, 102, 1200, 'completed', DATE '2024-05-12'),
  STRUCT(6, 3, 101, 80,  'completed', DATE '2024-06-15')
]);
SELECT * FROM scratch.purchases ORDER BY purchase_id;

For reference as you work: completed spend is Ava 380, Ben 25, Chloe 1280; Dan buys nothing; one purchase is refunded.

2. Your first CTE

A CTE is a named, temporary result set that exists only for the duration of one query. WITH defines it, and the query below uses it exactly like a table.

WITH completed_orders AS (
  SELECT purchase_id, customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
)
SELECT *
FROM completed_orders
ORDER BY amount DESC;

Five completed purchases come back, with the refund filtered out. The shape is the whole idea: name a step at the top, use it below. Nothing is stored anywhere; completed_orders evaporates the moment the query finishes.

3. Refactoring a subquery into a CTE

The same logic written both ways shows why CTEs exist. The subquery version buries the filtering logic in the middle of the FROM; the CTE version names it and moves it to the top.

-- BEFORE: inside-out, read from the middle
SELECT customer_id, SUM(amount) AS total
FROM (
  SELECT customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
) AS completed
GROUP BY customer_id;
-- AFTER: top-to-bottom, read like a recipe
WITH completed_orders AS (
  SELECT customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
)
SELECT customer_id, SUM(amount) AS total
FROM completed_orders
GROUP BY customer_id;

Identical results, identical cost. The difference is entirely for the human reader: in the CTE version you meet the step’s name and definition before it is used, so the query reads in the order the logic happens.

4. Chaining CTEs

Multiple CTEs are separated by commas, and each may reference the ones defined before it. This is how a transformation becomes a sequence of named steps.

WITH completed_orders AS (          -- step 1: keep the real sales
  SELECT customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
),
customer_spend AS (                 -- step 2: total per customer
  SELECT customer_id, SUM(amount) AS total_spend
  FROM completed_orders             -- references step 1
  GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_spend
ORDER BY total_spend DESC;

Filter, then aggregate, then present: three stages, each visible and named. Only the first WITH keyword appears; every further CTE is just name AS (...) after a comma. A later CTE can use any earlier one, but not the other way around.

5. Referencing the same CTE twice

A CTE can be used multiple times in the query below it, which a subquery cannot do without being copy-pasted. Here customer_spend is used once for the rows and once for the average.

WITH customer_spend AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM scratch.purchases
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT
  cs.customer_id,
  cs.total_spend,
  ROUND((SELECT AVG(total_spend) FROM customer_spend), 2) AS avg_spend,
  cs.total_spend > (SELECT AVG(total_spend) FROM customer_spend) AS above_avg
FROM customer_spend AS cs
ORDER BY cs.total_spend DESC;

The average completed spend is 561.67, and only Chloe clears it. Written with raw subqueries, the aggregation logic would appear three times; the CTE defines it once and every reference stays consistent, so a future change happens in exactly one place.

6. The classic pipeline: top customers by name

The article’s flagship example: aggregate spend, keep the big spenders, then join out for their names. Three steps, one readable query.

WITH customer_spend AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM scratch.purchases
  WHERE status = 'completed'
  GROUP BY customer_id
),
top_customers AS (
  SELECT customer_id, total_spend
  FROM customer_spend
  WHERE total_spend > 1000
)
SELECT c.name, t.total_spend
FROM top_customers AS t
JOIN scratch.customers AS c
  ON c.customer_id = t.customer_id
ORDER BY t.total_spend DESC;

Only Chloe, at 1280, crosses the 1000 threshold, and the final step attaches her name. Notice the final SELECT is trivial, just a join and a sort, because all the thinking happened in the named steps above it. That is the signature of a well-structured CTE query.

7. Top-N per group

CTEs pair naturally with window functions for “top N in each category” problems. Rank inside a CTE, filter on the rank outside, since a window function cannot appear directly in a WHERE.

WITH ranked_products AS (
  SELECT
    category,
    name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
  FROM scratch.products
)
SELECT category, name, price
FROM ranked_products
WHERE price_rank = 1      -- the dearest product in each category
ORDER BY category;

The Keyboard tops Accessories and the Monitor tops Displays. The two-step shape, rank in a CTE, filter on the rank outside, is the standard solution to every top-N-per-group question, and it only works because the CTE gives the ranked result a name you can filter.

8. Pre-aggregating for a dashboard

Dashboards often need several metrics at different grains. Compute each in its own CTE, then join them, which keeps every metric’s logic separate and testable.

WITH daily_revenue AS (
  SELECT purchase_date, SUM(amount) AS revenue
  FROM scratch.purchases
  WHERE status = 'completed'
  GROUP BY purchase_date
),
daily_orders AS (
  SELECT purchase_date, COUNT(*) AS orders
  FROM scratch.purchases
  WHERE status = 'completed'
  GROUP BY purchase_date
)
SELECT
  r.purchase_date,
  r.revenue,
  o.orders,
  ROUND(r.revenue / o.orders, 2) AS avg_order_value
FROM daily_revenue AS r
JOIN daily_orders AS o USING (purchase_date)
ORDER BY r.purchase_date;

Each day shows its revenue, order count, and average order value, with each metric built in isolation and combined at the end. When a metric definition changes, you edit one clearly named CTE rather than untangling a single monolithic query.

9. CTE versus temp table versus view

Three tools hold intermediate results, with three lifespans. A CTE lives inside one query, a temp table lives for your session, and a view lives permanently in the dataset.

-- CTE: exists only inside this one query
WITH spend AS (
  SELECT customer_id, SUM(amount) AS total
  FROM scratch.purchases GROUP BY customer_id
)
SELECT * FROM spend;
-- TEMP TABLE: persists for the session, reusable across queries
CREATE TEMP TABLE session_spend AS
SELECT customer_id, SUM(amount) AS total
FROM scratch.purchases GROUP BY customer_id;
SELECT * FROM session_spend WHERE total > 100;   -- a second, separate query can reuse it
-- VIEW: a permanent, shareable definition in the dataset
CREATE OR REPLACE VIEW scratch.customer_spend_view AS
SELECT customer_id, SUM(amount) AS total
FROM scratch.purchases GROUP BY customer_id;
SELECT * FROM scratch.customer_spend_view;

The rule of thumb: a CTE for logic that belongs to one query, a temp table when several queries in a session need the same intermediate result, and a view when the whole team should share one saved definition. Reaching for a view when a CTE would do clutters the dataset; reaching for a CTE when five queries repeat the same step wastes compute.

10. The best practices, applied

The final example is the guide’s advice in one query: descriptive names, one responsibility per CTE, explicit columns instead of SELECT *, and filtering as early as possible, which matters in BigQuery because you pay by bytes scanned.

WITH uk_customers AS (               -- one job: who is in scope
  SELECT customer_id, name
  FROM scratch.customers
  WHERE country = 'UK'
),
completed_purchases AS (             -- one job: which rows count (filter EARLY)
  SELECT customer_id, amount
  FROM scratch.purchases
  WHERE status = 'completed'
),
uk_spend AS (                        -- one job: the metric
  SELECT p.customer_id, SUM(p.amount) AS total_spend
  FROM completed_purchases AS p
  JOIN uk_customers AS u USING (customer_id)
  GROUP BY p.customer_id
)
SELECT u.name, s.total_spend         -- explicit columns, no SELECT *
FROM uk_spend AS s
JOIN uk_customers AS u USING (customer_id)
ORDER BY s.total_spend DESC;

Ava at 380 and Ben at 25, the two UK customers’ completed spend. Read the query top to bottom and each CTE answers one question, in the order you would explain the logic to a colleague. That is the entire case for CTEs: the computer does not need them, but every human who maintains your SQL after you does.

Work through these and you have the full article in practice: the WITH syntax, subquery refactoring, chaining, multiple references, the top-customers pipeline, top-N per group, dashboard pre-aggregation, and the CTE versus temp table versus view decision. The habit to keep is naming: a query built from steps called completed_purchases and uk_spend documents itself, and cleaner SQL and cheaper SQL usually turn out to be the same thing.

See you soon.