CTEs and subqueries

Opening your lesson

A common table expression, or CTE, names a query result for the rest of one statement. Write WITH name AS (...), then query that name. Each stage can establish a clear grain before the next stage joins or aggregates it. A CTE isn't a stored table, and repeating a CTE reference doesn't guarantee that BigQuery avoids repeated work.

A scalar subquery returns one value. You can compare a product price with (SELECT AVG(price) FROM shop.products) without hard-coding today's average. An EXISTS subquery answers whether at least one match exists and doesn't multiply the outer rows when there are several matches.

Give stages names that describe their rows, such as customer_orders or monthly_orders. Keep a count or other audit column while developing the query. It helps you catch a stage that changed the report grain unexpectedly.

WITH customer_orders AS (
 SELECT customer_id, COUNT(*) AS orders FROM shop.orders GROUP BY customer_id
)
SELECT customer_id, orders FROM customer_orders WHERE orders >= 5;

The CTE has one row per customer before the threshold is applied.

Choose your character

Your Work Gym account