CTEs: Readable Multi-Step Queries

Why nested subqueries get unreadable

A nested subquery forces you to read inside-out: find the innermost SELECT, understand it, then peel outward one layer at a time. Two levels deep and code review slows to a crawl. A Common Table Expression (CTE), written with WITH, names each step so the query reads top-to-bottom like a pipeline. This is not cosmetic. Production SQL, including every dbt model, is built as a chain of named CTEs precisely because named stages are reviewable, testable, and self-documenting.

A CTE is a named subquery, scoped to one statement

WITH name AS ( ... ) defines a temporary named result you can then query like a table. The demo below filters orders down to paid rows in a CTE called paid_orders, then aggregates revenue per customer from that CTE:

WITH paid_orders AS (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, SUM(total_cents) AS revenue
FROM paid_orders
GROUP BY customer_id;
-- customer_id | revenue
-- 1           | 7000
-- 2           | 1000
-- 3           | 6000

Customer 2's cancelled order for 9000 never reaches the aggregate, because the WHERE status = 'paid' filter lives inside the CTE. Without an ORDER BY the row order is not guaranteed, so add one when order matters.

Chaining stages: aggregate first, filter second

Each CTE can reference the ones defined above it, giving the staging, intermediate, mart shape:

WITH paid_orders AS (
  SELECT customer_id, total_cents FROM orders WHERE status = 'paid'
),
per_customer AS (
  SELECT customer_id, SUM(total_cents) AS revenue
  FROM paid_orders
  GROUP BY customer_id
)
SELECT customer_id, revenue
FROM per_customer
WHERE revenue > 5000
ORDER BY customer_id;

The payoff is that final WHERE revenue > 5000. You cannot filter on an aggregate in the same SELECT that computes it: WHERE SUM(total_cents) > 5000 fails with misuse of aggregate, because WHERE is evaluated before rows are grouped. The one-statement fix is HAVING, but splitting the aggregate into its own CTE lets the next stage treat revenue as an ordinary column and filter it with a plain WHERE. As stages pile up, that reads far better, and it is exactly the structure the Apply and Practice ask for.

Pitfalls

• Commas separate CTE definitions, but there is no comma before the final SELECT: ... ), per_customer AS ( ... ) SELECT ....
• A CTE is scoped to the single statement that begins with its WITH. A later, separate query cannot see it.
• Put each filter at the right stage. Row filters like status = 'paid' belong in the first CTE; aggregate filters (revenue, order_count) belong in a stage that reads the already-aggregated CTE.

Interview nuance: a CTE does not make a query faster on its own. Logically it is just a named subquery. Postgres 12 and later inline a non-recursive CTE that is referenced once, so it plans identically to the equivalent subquery, while Postgres 11 and earlier always materialized every CTE, which could actually be slower. Reach for CTEs to make a transform readable and reusable, not to optimize it.