Open any senior analyst's SQL file and you'll see the same shape: WITH step_one AS (…), step_two AS (…) SELECT … — a named pipeline, read top to bottom like a recipe. Subqueries gave you nesting; CTEs give you *narrative*. Same power, radically better readability.
What you'll learn
What
A Common Table Expression (CTE) is a named temporary result defined with WITH at the top of a query. The main query — and later CTEs in the same WITH list — can reference it by name, exactly as if it were a table that exists just for this one statement.
Why
Real analyses are multi-step: filter, aggregate, compare against the aggregate, rank. Nested subqueries express that inside-out — you read the deepest parenthesis first. CTEs express it top-down in execution order, each step named after what it means. That's the difference between SQL your team reviews in minutes and SQL nobody dares touch.
Where it's used
Every serious analytics codebase: funnel queries, cohort tables, metric definitions in dbt models (which are essentially CTE pipelines), deduplication passes, and recursive walks of org charts and category trees.
Where this runs in production
Analytics models are built as layered CTE chains — staging, filtering, aggregation, final select — reviewed like application code.
GitLab's open analytics codebase mandates CTE-first style: every transformation step named, no nested subqueries beyond trivial cases.
Product taxonomies nest dozens of levels deep; recursive CTEs expand a category's full subtree to aggregate sales at any node.
A chef doesn't chop onions inside the soup pot. They prepare labeled bowls first — chopped onions, minced garlic, measured stock — then combine them in order. CTEs are labeled bowls: german_customers, their_orders, monthly_totals. The final SELECT is the pot. Anyone can audit a bowl on its own; nobody can audit a soup where every ingredient was prepared mid-boil.
A nested subquery reads inside-out: the deepest block runs 'first' conceptually but is written in the middle of the statement. A CTE chain reads top-down in the same order it logically executes: step 1, step 2, final query. Reviewers verify each step against its name and move on — which is why style guides at data-mature companies mandate CTEs over nesting beyond one level.
WITH shipped AS ( SELECT * FROM orders WHERE status = 'shipped' ), customer_totals AS ( SELECT customer_id, SUM(amount) AS total FROM shipped GROUP BY customer_id ) SELECT customer_id, total FROM customer_totals WHERE total > 1000 ORDER BY total DESC;
A two-step pipeline: filter, then aggregate, then the main query filters the aggregate. Note customer_totals reads FROM shipped — later CTEs build on earlier ones. Also note the HAVING-avoidance: filtering an aggregate in a later step (WHERE total > 1000) is equivalent to HAVING and often clearer in pipelines.
A CTE exists only inside its statement — it is not a temp table, not a view, and leaves nothing behind. Two queries needing the same intermediate result must each define it (or promote it to a view/dbt model, the standard evolution when a CTE gets copy-pasted a third time). Within one statement, a CTE may be referenced multiple times — the classic advantage over repeating a subquery verbatim.
WITH RECURSIVE org AS ( SELECT employee_id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL -- anchor: the CEO UNION ALL SELECT e.employee_id, e.name, e.manager_id, org.depth + 1 FROM employees e INNER JOIN org ON e.manager_id = org.employee_id -- next level ) SELECT name, depth FROM org ORDER BY depth, name;
Read it as levels: the anchor emits the root (depth 1); each pass joins employees to the rows produced so far, emitting their direct reports one level deeper; recursion stops when a pass adds nothing. The depth counter is both useful output and your safety rail — add WHERE depth < 20 while developing to guard against cycles in dirty data.
If a corrupted row makes an employee (transitively) their own manager, the recursive member keeps producing rows and the query runs until it exhausts memory or a timeout. Defenses: a depth cap in the recursive member, or (PostgreSQL 14+) the CYCLE clause; production hierarchies also deserve a constraint-level guarantee. Treat any recursive CTE without a depth guard as a code-review finding.
The same three-step analysis, restructured. Click each node — the CTE chain runs top-down, and each step is independently testable.
Count shipped orders per country — with the filter step named instead of inlined.
The CTE isolates 'which rows are in scope' from 'what we compute'. Functionally identical to putting WHERE in the main query — the value is structural: as the analysis grows, the scope definition stays in one named place instead of being repeated in five queries' WHERE clauses.
country | orders ---------+-------- DE | 260 US | 230 FR | 130 (3 rows)
Your task
Build a two-step pipeline over orders(order_id, customer_id, amount, status): step 1 (CTE shipped) keeps shipped orders; step 2 (CTE customer_totals) sums amount per customer from shipped as total. The final query returns customer_id and total for customers with total > 250, highest first. Fill in the blanks.
customer_id | total
-------------+--------
10 | 300.00
12 | 260.00
(2 rows)Write your solution in the editor on the right, then hit Run.
How long does a CTE exist?
In WITH a AS (…), b AS (…) SELECT … — which references are legal?
Why does the deduplication idiom put ROW_NUMBER() in a CTE and filter rn = 1 in the outer query?
In a recursive CTE, what stops the recursion?
What is the most likely cause, and the right defensive fix?
Using two chained CTEs over orders(order_id, customer_id, amount): (1) customer_totals — total spend per customer; (2) grand — the single overall average of those totals. Final query: each customer_id, total, and their share vs the average as pct_of_avg = ROUND(total / avg_total * 100, 0). Sort by total descending.
When do you reach for a CTE over a subquery, and is there a performance difference?
My default is: one level of nesting for a trivial scalar or IN-list is fine as a subquery; anything with two or more steps, anything a teammate will review, and anything where a step deserves a name becomes a CTE pipeline. CTEs read top-down in execution order, make each step independently runnable for debugging, and let one intermediate result be referenced several times without duplication. On performance: in modern engines the two forms usually compile to the same plan — PostgreSQL 12+ inlines CTEs like subqueries. The nuance worth mentioning is materialization: a CTE referenced multiple times may be computed once and reused, which can help or hurt, and a materialized CTE acts as an optimization fence blocking predicate pushdown; PostgreSQL exposes AS MATERIALIZED / NOT MATERIALIZED to choose. So: readability decides the default, EXPLAIN decides the exceptions.
Explain how a recursive CTE works, using an org chart as the example.
A recursive CTE has two members joined by UNION ALL. The anchor runs once and seeds the result — for an org chart, the CEO row (or any subtree root), typically with a level counter of 0. The recursive member references the CTE's own name: it joins the base table to the rows produced by the previous iteration — employees whose manager_id is in the current frontier — emitting the next level with level + 1. The engine iterates until a pass yields zero new rows, and the final result is the union of every pass: the whole tree, each row annotated with its depth. Two production notes: seed the anchor with any node to get 'the subtree under X', which fixed-depth self joins cannot express for unknown depth; and always guard against cycles in dirty data with a depth cap or the CYCLE clause, because a corrupted manager pointer otherwise makes the recursion run forever.
Your team's 400-line report query is an unreadable nest of subqueries. How would you refactor it, and how do you make sure you didn't change the results?
I refactor inside-out: find the deepest subquery, lift it to the top as the first CTE named for its business meaning, and repeat until the main query is a short SELECT over named steps — mechanical transformations only, no 'improvements' mixed in, because simultaneous refactor-and-fix makes regressions undiagnosable. Verification is a reconciliation harness: run old and new on the same data and compare row counts, checksums of key aggregates (SUM of measures, COUNT DISTINCT of keys), and ideally a full EXCEPT in both directions, which returns zero rows when the outputs are identical. Then I add one micro-test per CTE — each step is now independently runnable, so I can assert 'shipped has no cancelled rows' directly. That testability is the real payoff of the refactor: the next change gets reviewed step by step instead of by reputation. If several reports share the early steps, I'd promote those CTEs into views or dbt models so the logic exists once.
Common Mistakes to Avoid
1) Writing WITH before every CTE — WITH appears once; later CTEs are just name AS (…) after a comma. 2) Referencing a CTE defined later in the list — text order is dependency order; reorder the steps. 3) Expecting a CTE to exist in the next statement — it dies with its statement; promote reused logic to a view. 4) Filtering a window function's result in the same SELECT (WHERE rn = 1 beside the ROW_NUMBER) — compute in a CTE, filter one level up. 5) Shipping a recursive CTE with no depth guard — clean data today doesn't prevent a cyclic manager_id tomorrow, and the failure mode is an unkillable query.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: CTEs with a cooking mise-en-place analogy of your own.' • 'Give me a three-level nested query and coach me through refactoring it to CTEs.' • 'Trace a recursive CTE over a 6-employee org chart, iteration by iteration.' • 'When does CTE materialization help and when does it hurt? Small examples.' • 'Interview mode: have me design a funnel query with CTEs and critique my step names.'
Glossary
CTE (Common Table Expression) — a named intermediate result defined with WITH, scoped to one statement. WITH list — the comma-separated sequence of CTE definitions before the main query. Pipeline style — structuring analysis as chained, named CTE steps read top-down. Forward reference — each CTE may read earlier CTEs only. Materialization — computing a CTE once and reusing the stored result; an optimization fence for predicate pushdown. Inlining — the optimizer merging a CTE's body into the outer plan like a subquery. Recursive CTE — WITH RECURSIVE; anchor + recursive member glued by UNION ALL. Anchor — the seed rows, run once. Recursive member — the self-referencing half producing the next level per iteration. Depth guard — a level cap protecting against cycles. Deduplication idiom — ROW_NUMBER in a CTE, WHERE rn = 1 outside. View — a persistent named query; the promotion target for CTEs needed across statements.
Recommended Resources
• Docs: PostgreSQL manual — 'WITH Queries (Common Table Expressions)', including the MATERIALIZED options and CYCLE clause. • Read: GitLab's public SQL style guide for a production CTE-first standard, and the dbt docs' 'refactoring legacy SQL' guide. • Practice: take your gnarliest saved query and refactor it into named steps, verifying with EXCEPT in both directions; then write one recursive walk over a toy org chart. • Next in DSM: the deduplication example smuggled in ROW_NUMBER() OVER — Window Functions in SQL opens that toolbox properly: ranks, lags, and running totals without collapsing rows.
Recap
✓ WITH name AS (…) names an intermediate result for one statement; later CTEs and the main query read it like a table. ✓ CTEs chain forward — each step may reference earlier steps — turning nested logic into a top-down pipeline in execution order. ✓ Scope is a single statement: no side effects, nothing persists; promote reused CTEs to views. ✓ Performance is usually identical to subqueries (inlining); materialization can help multi-reference CTEs or hurt pushdown — EXPLAIN decides. ✓ The dedup idiom — rank in a CTE, filter rn = 1 outside — exists because WHERE can't see same-SELECT window results. ✓ WITH RECURSIVE = anchor + self-referencing member + UNION ALL, iterating until no new rows; always guard depth against cycles. Next up: Window Functions in SQL. You've now used ROW_NUMBER once as a guest star — next it headlines: PARTITION BY, ranking families, LAG/LEAD time travel, and running totals, all without collapsing a single row.
Run your code to see the output here.