Lesson 5 of 8 · 48 min

CTEs, recursion & SQL debugging

Staged CTE style, materialization caveats, recursive hierarchies, fan-out detectors, broken-query clinic, EXPLAIN altitude.

Lesson 5 · CTEs & debugging

Structure complex SQL and fix it under the microscope

Long SQL without stages is un-reviewable

Senior answers use CTEs as a proof: each stage has a named grain and a check. Debugging interviews (broken query, wrong total, timeout) reward a method: simplify, test intermediate counts, bisect joins, compare to a toy example. This lesson is that method plus recursive CTE literacy.

A CTE (WITH clause) is a named subquery valid for one statement. Use it to document grain, reuse logic, and isolate filters. Prefer readable staged SQL over one nested monster - unless the dialect materializes CTEs poorly and the interviewer asks for optimization.

CTE style that interviews like

sql

1WITH paid_orders AS (               -- grain: one row per paid order2  SELECT order_id, user_id, amount, created_at3  FROM orders4  WHERE status = 'paid'5),6user_month AS (                     -- grain: user × month7  SELECT8    user_id,9    DATE_TRUNC('month', created_at) AS month,10    SUM(amount) AS gmv,11    COUNT(*) AS n_orders12  FROM paid_orders13  GROUP BY 1, 214),15with_prev AS (                      -- decorate user-month16  SELECT *,17    LAG(gmv) OVER (18      PARTITION BY user_id ORDER BY month19    ) AS prev_gmv20  FROM user_month21)22SELECT *,23  gmv - prev_gmv AS gmv_delta24FROM with_prev25WHERE month = DATE '2026-01-01';

Materialization and optimization awareness

Postgres historically often inlined CTEs; newer versions may materialize depending on clauses. BigQuery/Snowflake have their own rules. Interview altitude: CTEs are for clarity first; if a CTE is scanned many times and heavy, consider temp tables or rewriting. Do not cargo-cult 'CTEs are always slow' or 'always free'.

Recursive CTEs

Recursive CTEs walk graphs: org charts, category trees, bill-of-materials, permission inheritance. Structure: base anchor UNION ALL recursive step referencing the CTE. Always discuss termination (depth limit, cycle detection) or you loop forever.

sql

1-- Org chart: employee → managers upward2WITH RECURSIVE chain AS (3  SELECT employee_id, manager_id, name, 0 AS depth4  FROM employees5  WHERE employee_id = 42          -- anchor6  UNION ALL7  SELECT e.employee_id, e.manager_id, e.name, c.depth + 18  FROM employees e9  JOIN chain c ON e.employee_id = c.manager_id10  WHERE c.depth < 20             -- safety cap11)12SELECT * FROM chain;1314-- Hierarchy downward: children of a node15WITH RECURSIVE tree AS (16  SELECT id, parent_id, name, 1 AS depth17  FROM categories WHERE id = 118  UNION ALL19  SELECT c.id, c.parent_id, c.name, t.depth + 120  FROM categories c21  JOIN tree t ON c.parent_id = t.id22  WHERE t.depth < 1023)24SELECT * FROM tree;

Debugging checklist (use live)

When the number is wrong: (1) print COUNT(*) at each CTE, (2) compare distinct keys vs rows (fan-out detector), (3) check null rates on join keys, (4) recompute metric on a hand-built 3-row fixture, (5) remove filters until the total matches a known baseline, then re-add. Narrate this checklist - it is a hire signal even before the fix.

sql

1-- Fan-out detector2SELECT3  COUNT(*) AS n_rows,4  COUNT(DISTINCT order_id) AS n_orders5FROM joined;6-- if n_rows >> n_orders, a join exploded orders78-- Null key detector9SELECT10  COUNT(*) FILTER (WHERE a_id IS NULL) AS a_null,11  COUNT(*) FILTER (WHERE b_id IS NULL) AS b_null12FROM joined;1314-- Distribution sniff15SELECT status, COUNT(*) FROM orders GROUP BY 1 ORDER BY 2 DESC;

Broken query clinic (common exam prompts)

sql

1-- BUG 1: double count from join then sum parent2SELECT SUM(o.amount)3FROM orders o4JOIN order_items i ON i.order_id = o.order_id;5-- FIX: SUM at order grain first or sum item prices67-- BUG 2: left join + where on right8SELECT u.user_id, COUNT(o.order_id)9FROM users u10LEFT JOIN orders o ON o.user_id = u.user_id11WHERE o.created_at >= DATE '2026-01-01'12GROUP BY u.user_id;13-- FIX: move date filter into ON, or filter orders in CTE first1415-- BUG 3: NOT IN with nulls16WHERE id NOT IN (SELECT user_id FROM deleted);  -- deleted.user_id nulls17-- FIX: NOT EXISTS1819-- BUG 4: average of averages for global metric20-- FIX: sum/sum2122-- BUG 5: window filter in WHERE23-- FIX: qualify/subquery

EXPLAIN at interview altitude

Say: look for nested loop on large sets without index, hash join memory, unexpected row estimates (stats stale), and sorts for windows. Propose indexes matching WHERE/JOIN/ORDER BY leading columns. Mention covering indexes only if you know the dialect well.

sql

1EXPLAIN (ANALYZE, BUFFERS)2SELECT ...;34-- Interview talking points5-- * rows estimated vs actual (big miss → stats / bad join order)6-- * nested loop × large inner → need index or rewrite7-- * sort nodes for ORDER BY/window - can dominate8-- * parallel seq scan sometimes fine for warehouse-ish local PG

Debugging narration script

01"How structure complex SQL?" → CTEs per grain; comment filters; final select thin.
02"Number too high?" → fan-out detector COUNT vs COUNT DISTINCT on parent key.
03"Number too low?" → inner join dropped rows; WHERE on left join; overly tight filters; timezone.
04"Recursive CTE risk?" → cycles and non-termination; add depth cap / cycle path array.
05"CTE slow?" → dialect materialization; may inline or not; rewrite if measured hot.
06"First debug step?" → intermediate counts + tiny fixture before changing business logic.
07"EXPLAIN meaning?" → operators + row estimates; propose indexes from predicates.
08"Name CTEs how?" → by grain/business meaning, not tmp1.
09"When subquery vs CTE?" → CTE for multi-use clarity; subquery fine for single-use filters.
10"Production habit?" → assert row counts in dbt tests / unit SQL fixtures for metrics.

docsPostgreSQL WITH queries (CTEs)PostgreSQL docsPostgreSQL EXPLAINPostgreSQL docsdbt - testing SQL models (engineering culture)dbt Labs articleMode SQL tutorial - subqueriesMode

Interview habit: before you write a join or window, say the grain of the result set out loud (one row per what?), name the filters that apply before aggregation, and sketch a 4-row toy table. That 30-second ritual catches double-counting and null traps faster than staring at syntax.

When stuck live, reduce to two tables and three rows on the whiteboard. Prove the join, then reintroduce filters and aggregates. Interviewers credit recovery more than a first-draft perfect query that you cannot explain.

State timezone and late-arriving data assumptions once. 'UTC calendar day of paid_at; events may land +2h' prevents half the metric arguments in marketplace and consumer analytics rounds.

Prefer COUNT(DISTINCT key) or EXISTS for existence metrics; prefer SUM of a measure that already lives at the target grain. If you must join a child table, aggregate the child in a CTE first and say why.

Read the English noun in the question: users, orders, sessions, days. That noun is usually the GROUP BY or partition key. If your SELECT invents a different noun, you probably changed the grain by accident.

Null policy belongs in the spoken answer: 'missing country is excluded from geo breakouts but included in totals' is a product decision. Silent COALESCE to 'unknown' without saying so is how dashboards disagree.

For top-N, always name the tie-break (latest id, lowest sku, highest created_at). Nondeterministic winners fail both unit tests and interviewer follow-ups about flaky results.

Close every SQL answer with two edges: empty input and a duplicate natural key. If both survive your mental dry-run, you are ready for the next prompt in a multi-question screen.

Checkpoint

A staged query's CTE paid_orders is one row per order; after joining items, COUNT(*) is 4× COUNT(DISTINCT order_id). Diagnosis?

ACOUNT DISTINCT is broken in this engineBJoin to items fan-out: ~4 items per order average; aggregates on order measures must not run at item grainCNeed UNION instead of JOIN to items

Checkpoint

Recursive CTE walking manager_id without a depth limit on a cyclic bad row - risk?

AIt automatically stops at the root even with cyclesBNon-termination / resource blowup; add depth cap and/or cycle detection on the pathCUNION (distinct) in recursion always removes cycles safely

Checkpoint

Best first move when a take-home metric is 12% above the provided answer key?

AMultiply final result by 0.88 to match the keyBBisect: count rows per CTE, check fan-out, re-read metric definition (refunds/tax/timeframe), recompute on a tiny fixtureCRemove all WHERE filters to maximize rows until it 'feels' right

Checkpoint

When is a CTE preferable to a deeply nested subquery in an interview?

ANever - nested subqueries always optimize betterBWhen you want named intermediate grains, reuse, and stepwise debug narrationCOnly when using recursive queries; non-recursive CTEs are illegal

Checkpoint

EXPLAIN shows nested loop with inner index scan estimated 1 row but actual 50k per outer. What do you say?

ANested loops are always wrong; force a hash join hint immediatelyBCardinality misestimate: join selectivity/stats off; risk is 50k× outer rows work; fix stats, rewrite predicates, or adjust join strategy after measuringCActual rows do not matter if the query eventually returns

Can you stage a multi-CTE metric, run fan-out detectors, and sketch a safe recursive walk?

New to itGetting thereConfident

Takeaways

CTEs name grains - that is documentation under stress.
Debug with intermediate counts and tiny fixtures.
Fan-out detector: COUNT vs COUNT DISTINCT.
Recursive SQL needs termination strategy.
EXPLAIN = estimates vs actuals, then grounded index/join talk.

Next: OLTP schema design interviews - keys, normalization, and tradeoffs you must defend.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free