SQL Roadmap/Subqueries & CTEs

CTEs & Recursive Queries

The WITH clause names a subquery so it can be referenced (and read) like a real table — and WITH RECURSIVE extends that to self-referencing queries, the only clean way to walk a hierarchy of unknown depth.

!!4/5Theory: 25m2 problems

CTEs: naming a subquery so it reads like a table

A Common Table Expression, introduced with WITH, gives a subquery a name and lets you reference it later in the query exactly as if it were a real table:

WITH department_averages AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT e.name, e.salary, da.avg_salary FROM employees e JOIN department_averages da ON e.department_id = da.department_id WHERE e.salary > da.avg_salary;

Nothing here is expressible any differently than an equivalent subquery nested directly in the FROM clause — a CTE is (on most engines, most of the time) purely a readability and organization tool, not a different execution strategy. Its real value shows up as queries grow: you can define multiple CTEs, separated by commas, each one able to reference the ones defined before it, turning a deeply nested tangle of subqueries into a linear, top-to-bottom sequence of named steps that reads almost like a small program. This alone is worth adopting as a default style for any query with more than one layer of subquery, interview or not — a reviewer (or an interviewer watching you type) can follow "first compute X, then use X to compute Y" far more easily than a subquery nested three levels deep.

WITH RECURSIVE: the one thing nothing else can do cleanly

Every technique covered so far — joins, correlated subqueries, plain CTEs — operates on a fixed, known number of "hops" through the data. A self-join finds direct managers; nesting two self-joins finds managers-of-managers; but if you don't know in advance how many levels deep a hierarchy goes (an org chart with a variable number of management layers, a category tree, a "who recommended this member" chain), no fixed number of self-joins can handle every case — you'd need as many self-joins as the deepest possible chain, which you may not even know ahead of time.

WITH RECURSIVE solves exactly this by letting a CTE reference itself:

WITH RECURSIVE management_chain AS ( -- Base case: start with the employee in question SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 101 UNION ALL -- Recursive case: join the chain-so-far back to employees, -- walking one level further up the management tree each iteration SELECT e.id, e.name, e.manager_id, mc.depth + 1 FROM employees e JOIN management_chain mc ON e.id = mc.manager_id ) SELECT * FROM management_chain;

Two parts, always: a base case (or "anchor member") that seeds the starting row(s), and a recursive case that joins the CTE to itself, each iteration consuming the previous iteration's result and producing the next "layer." The engine repeats the recursive part automatically, feeding each pass's output back in as the next pass's input, until a pass produces zero new rows — at which point recursion stops and every accumulated row across all passes is the final result (UNION ALL is what accumulates them; UNION would additionally dedupe, which is occasionally useful for cycle-prone data but changes the semantics).

The runaway-recursion guard

Because the stopping condition is implicit ("no new rows produced"), a WITH RECURSIVE query over cyclic data (A recommends B, B recommends A) can loop forever unless the recursive case explicitly guards against revisiting a row — typically by tracking visited IDs in an array/path column and adding WHERE NOT (e.id = ANY(mc.visited_ids)) to the recursive branch. Most engines also support a hard depth cap (PostgreSQL: nothing built-in beyond query timeouts; SQL Server: OPTION (MAXRECURSION n)) as a safety net — knowing this guard exists, and why, is worth more in an interview than getting the exact syntax right, since it's the detail that separates "I've read about recursive CTEs" from "I've actually had to debug one against real, possibly-cyclic data."

Why interviewers care

Recursive CTEs are a deliberately rare topic in day-to-day query writing — most schemas don't have variable-depth hierarchies — which is exactly why they're a good differentiator question: a candidate who can correctly identify "this needs a recursive CTE, not another self-join" on a hierarchy-shaped problem, and can sketch the base-case/recursive-case structure without looking it up, has clearly worked with genuinely tree-shaped production data before, not just flat tables.

Pitfalls and interview gotchas

  • Using UNION instead of UNION ALL in the recursive definition without a specific reason — UNION's automatic deduplication silently changes which rows can further recurse in ways that are easy to get subtly wrong.
  • Forgetting a cycle guard on data that could plausibly contain cycles, risking infinite recursion.
  • Reaching for WITH RECURSIVE for a fixed, known-depth relationship (e.g., "employee's direct manager") where a plain self-join is simpler and clearer — recursion is for genuinely unbounded depth, not a bigger hammer for every hierarchy-flavored question.
  • Forgetting that the recursive term can only reference the CTE's own name once, and cannot contain aggregate functions, DISTINCT, or an outer ORDER BY/LIMIT inside the recursive branch itself on most engines — these restrictions exist because the engine needs to run the recursive branch repeatedly against a growing, not-yet-final result set.

Where this goes next

Window Functions (next) is the modern alternative to a large share of what correlated subqueries do — ranking, per-group comparisons, running totals — computed in a single pass over the data instead of one subquery execution per row, and is the single highest-leverage topic on this roadmap for standing out in a senior-level SQL interview.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked