🗄 SQL & Databases · Chapter 4

CTEs and readable query structure

A 200-line query nobody can read is not clever. Name each step and the same logic becomes six paragraphs anyone can review.

Study this lesson free in the app →

The basics

A CTE (Common Table Expression) is a named, temporary result you define at the top of a query with WITH name AS (...) and then use like a table further down.

The problem it solves: real analytics questions take several steps — filter the rides, remove duplicates, aggregate per city, compare to a benchmark. Written as nested subqueries, you must read the query from the innermost bracket outwards, and when the final number is wrong there's nothing to point at. CTEs turn the same logic into a top-to-bottom list of named steps.

The mental model: a recipe card. Step 1: completed rides. Step 2: deduplicated. Step 3: daily totals. Final: the report. Each step reads only from earlier steps, and you can "taste" any step by pointing the final SELECT at it. In modern engines (Postgres 12+, Snowflake, BigQuery) this costs nothing — the optimiser inlines the steps.

Explained

-- what you inherit
SELECT * FROM (SELECT a.*, b.x FROM (SELECT ... FROM rides WHERE ...) a
  LEFT JOIN (SELECT ... FROM drivers GROUP BY ...) b ON ...) c
WHERE c.x > (SELECT avg(x) FROM ...);

-- what you should write
WITH completed_rides AS ( ... ),
     driver_totals   AS ( ... ),
     joined          AS ( ... )
SELECT * FROM joined WHERE x > (SELECT avg_x FROM benchmark);

Same result, same speed. The difference is that the second one can be read from the top down, and each step has a name that says what it is.

The recipe with no steps

Imagine a recipe written as one sentence: "combine the flour which you have sieved with the butter that was softened after being removed from the fridge which should have been done an hour earlier with the sugar…"

Everything you need is there. Nobody can cook from it.

A recipe works because it's a sequence of named steps: make the dough, rest it, roll it, bake it. You can check one step without holding the whole thing in your head, and if the result is wrong you know which step to look at.

Nested subqueries are the one-sentence recipe. CTEs are the numbered steps — and the reason they matter isn't beauty, it's debuggability: you can run any step on its own and see what it produced.

The shape of a readable query

WITH
-- 1. narrow the source down first
completed AS (
  SELECT ride_id, driver_id, city, fare, ride_date
  FROM rides
  WHERE status = 'completed'
    AND ride_date >= '2026-07-01'
),

-- 2. dedup before anything is counted
deduped AS (
  SELECT DISTINCT ON (ride_id) *
  FROM completed
  ORDER BY ride_id, ride_date DESC
),

-- 3. aggregate to the grain you actually need
daily AS (
  SELECT city, ride_date,
         count(*)  AS rides,
         sum(fare) AS revenue
  FROM deduped
  GROUP BY city, ride_date
)

-- 4. final shape
SELECT * FROM daily ORDER BY ride_date, revenue DESC;

The convention that makes queries reviewable:

1. Import — one CTE per source table, filtered and narrowed
2. Transform — one CTE per logical step, named for what it produces
3. Final select — as simple as possible, ideally just SELECT * FROM last_step

Name CTEs after what they contain, not what they do: completed_rides beats step2 or filtered.

Step through a CTE chain

Each CTE is a checkpoint. When a number is wrong, you bisect the query by changing one line.

Which one is wrong?

Two queries, meant to compute revenue for completed rides after deduplicating.

A

WITH deduped AS (SELECT DISTINCT ON (ride_id) * FROM rides ORDER BY ride_id, event_time DESC)
SELECT city, sum(fare) FROM deduped WHERE status = 'completed' GROUP BY city;

B

WITH completed AS (SELECT * FROM rides WHERE status = 'completed'),
     deduped   AS (SELECT DISTINCT ON (ride_id) * FROM completed ORDER BY ride_id, event_time DESC)
SELECT city, sum(fare) FROM deduped GROUP BY city;

A ride can have several events: requested, then completed, then a later cancelled correction.

Do CTEs cost performance?

The honest answer: it depends on the engine, and much less than people think.

• Postgres 12+, Snowflake, BigQuery, Databricks, DuckDB — CTEs are generally inlined and optimised exactly like a subquery. No cost
• Postgres 11 and earlier — CTEs were an optimisation fence: always materialised, filters could not be pushed into them. This is the origin of "CTEs are slow", and it's been outdated since 2019
• A CTE referenced multiple times may be computed more than once, or materialised — engine-dependent. If it's expensive and reused, that's when to check

Modern Postgres lets you say which you want:

WITH heavy AS MATERIALIZED   ( ... )   -- compute once, reuse
WITH light AS NOT MATERIALIZED ( ... ) -- inline it

The practical position: write CTEs for readability by default. If a query is slow, read the plan (next lesson) rather than assuming the CTEs caused it — the cause is almost always a join, a missing filter or a shuffle, not the way you named your steps.

And the asymmetry worth remembering: an unreadable query that's 5% faster gets rewritten wrongly by the next person. Readability compounds; a 5% saving doesn't.

Recursive CTEs — the one exotic use

A CTE can reference itself, which is how you walk a hierarchy or generate a series.

-- generate a date spine: every day in July, including days with no data
WITH RECURSIVE dates AS (
  SELECT DATE '2026-07-01' AS d          -- anchor: where it starts
  UNION ALL
  SELECT d + 1 FROM dates WHERE d < DATE '2026-07-31'   -- recursive step
)
SELECT d, coalesce(r.rides, 0) AS rides
FROM dates
LEFT JOIN daily_rides r ON r.ride_date = dates.d;

That date spine is genuinely useful: without it, days with zero rides simply don't appear in your output, and a chart silently skips them.

The other real use is walking a hierarchy — an org chart, a category tree, a chain of referrals:

WITH RECURSIVE reports AS (
  SELECT emp_id, manager_id, 1 AS depth FROM employees WHERE emp_id = 42
  UNION ALL
  SELECT e.emp_id, e.manager_id, r.depth + 1
  FROM employees e JOIN reports r ON e.manager_id = r.emp_id
)
SELECT * FROM reports;

Always bound the recursion. A cycle in the data (an employee who manages their own manager) will run forever without a depth limit.

Import, transform, final select. One CTE per logical step, named for what it contains.

Debug by swapping the final SELECT to any intermediate step.

"CTEs are slow" is outdated — true for Postgres ≤11 only.

Words to know

In two minutes

• A CTE is a named step: WITH name AS (query), usable by later steps and the final SELECT.
• The point is debuggability — swap the final SELECT for any step to see what it produced.
• Shape: import CTEs (one per source, filtered), transform CTEs (one idea each), then a thin final SELECT.
• Step order changes answers: filter-then-dedup and dedup-then-filter give different numbers.
• Modern engines inline CTEs like subqueries; 'CTEs are slow' was true only for Postgres 11 and earlier.
• Recursive CTEs (anchor UNION ALL recursive step) walk hierarchies and build date spines; always add a stop condition.
• Name CTEs for what they contain; if the name needs 'and', split the step.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.