Common Table Expressions (CTEs)

A Common Table Expression is a named subquery written at the top of your statement with one keyword, WITH. Once defined, the rest of the query treats it like a table.

Why it matters: nested subqueries force you to write inside-out — the reader has to find the deepest parentheses, then walk outward. A CTE lets you write top-down, in the order you would describe the problem aloud. We will use these two tables (each order carries its revenue in amount):

users id	name	country
1	Asha	IN
2	Bo	CN
3	Carlos	US
4	Diya	IN

orders	user_id	amount
101	1	30
102	1	900
103	3	20
104	4	50

The same query, read top-down

WITH user_spend AS (
  SELECT u.id, u.country, COALESCE(SUM(o.amount), 0) AS spend
  FROM   users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.country
)
SELECT country, AVG(spend) AS avg_spend
FROM   user_spend
GROUP BY country
ORDER BY avg_spend DESC;

country	avg_spend
IN	490
US	20
CN	0

The CTE says, in plain order, “first compute each user’s spend, then average it per country.” Asha’s 930 and Diya’s 50 average to 490 for IN; Bo, who never ordered, is kept at 0 by the LEFT JOIN. That reads exactly how you would explain it to a colleague.

Chaining steps

Several CTEs can sit in one WITH, comma-separated, each referencing the ones before it. Here is a three-step pipeline — spend per user, then the top spender in each country:

WITH user_totals AS (
  SELECT u.id, u.name, u.country, COALESCE(SUM(o.amount), 0) AS total_spend
  FROM   users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.name, u.country
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_spend DESC) AS rn
  FROM   user_totals
)
SELECT country, name, total_spend
FROM   ranked
WHERE  rn = 1;

country	name	total_spend
IN	Asha	930
US	Carlos	20
CN	Bo	0

Each named step stands alone — you can swap the final SELECT for SELECT * FROM user_totals to inspect that stage. That debuggability is something nested subqueries simply cannot give you. The same shape builds the classic funnel: a signed_up CTE, a purchased CTE, a repeat_buyers CTE, each a stage, collapsed into one row of counts at the end (here: 4 signed up, 3 purchased, 1 repeat buyer).

	CTE	Subquery
Reusable by name in the same query	Yes	No (must repeat)
Reads top-down	Yes	No (inside-out)
Can be recursive	Yes (WITH RECURSIVE)	No
Inspectable in isolation	Yes	Hard

The reuse point is the real payoff: define user_spend once and reference it three times (country average, the global average, and their difference) — with subqueries you would paste the same five lines three times, and the next reader (probably you, in three months) would not thank you.

Practice

Quick check

0/3

Q1What does a CTE give you that a subquery doesn't?

Better performance alwaysA named result you can reference multiple times in one statementAutomatic indexingThe ability to use window functions

Check answer

Q2Why do CTEs make multi-step analysis more debuggable?

They're cached automaticallyYou can swap the final SELECT for SELECT * FROM step_n to inspect any stageThey run in parallelThey print intermediate results

Check answer

Q3On MySQL 8 or older Postgres, what's a CTE performance gotcha?

They can't use indexesThey may be materialised — an optimisation fence that stops the planner pushing filters inThey lock the source tablesThey run once per session

Check answer