Recursive CTEs in SQL
A recursive CTE defines a query that references itself, allowing it to accumulate rows iteratively until a termination condition is met. Recursive CTEs handle hierarchical and graph-structured data where the depth of traversal is unknown at query-write time and cannot be expressed with a fixed number of joins.
A recursive CTE lets a query reference itself, accumulating rows iteratively until nothing new turns up. It's designed for hierarchical data — org charts, category trees, folder structures — where the depth isn't known in advance and can't be handled with a fixed number of joins.
The structure has two parts separated by UNION ALL: an anchor member and a recursive member. The anchor produces the starting rows. The recursive member references the CTE by name and extends the result by one step per iteration. PostgreSQL runs the anchor first, then keeps running the recursive member using the rows from the previous pass, until no new rows are produced.
The mechanics are easiest to see with a simple counter first:
The same structure traverses the org chart — the anchor is the root employee, each recursive step adds one level:
WITH RECURSIVE org_hierarchy AS (
-- Anchor: start from the top (no manager)
SELECT employee_id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: add each employee whose manager is already in the result
SELECT e.employee_id, e.name, e.manager_id, oh.depth + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, name, depth
FROM org_hierarchy
ORDER BY depth, nameThe anchor selects the root: the employee with no manager. The recursive member joins each subsequent employee to whoever is already in the accumulated result, matching their manager_id to an employee_id already found. Each pass adds one level. Execution stops when no employees remain that can be joined to the current set.
The depth column is a counter that increments by one per level. Carrying a counter through the recursion is standard — useful for output ordering and for enforcing a termination limit.
The one thing that trips people up
A recursive CTE terminates naturally when the recursive member returns zero new rows. That works correctly for clean tree structures. But if the data has cycles — a manager who reports to someone who reports back to them — the recursion never terminates and the query runs until it hits a resource limit.
PostgreSQL doesn't detect cycles automatically. The practical defense is a depth limit in the recursive member's WHERE clause:
WHERE oh.depth < 20This stops recursion at 20 levels regardless of whether the data has further rows. It's a blunt guard, but it's reliable when you know a reasonable maximum depth.
Also: the RECURSIVE keyword in WITH RECURSIVE is required even when the recursion seems obvious. PostgreSQL uses it as a signal to enable the iterative evaluation mechanism. Leave it out and the query errors.
generate_series vs recursive CTEs
Use generate_series() when the sequence structure is known before the query runs — every day from A to B, every integer from 1 to N. Use a recursive CTE when the structure is encoded in the data and must be discovered during execution — traverse this org chart to whatever depth the data has.
10 Recursive CTEs practice problems
Write a query to return a sequence of batch identifiers from `1` through `5`, with each value appearing as a separate row.
Write a query to return the ID, name, and depth level for every category in the hierarchy.
Write a query to return the ID, name, and depth level for every employee in those top two levels.
Write a query to return the ID, name, and depth level for every employee.
Write a query to return every depth level and the number of employees at that level.
Write a query to return the ID and name of every employee who reports to manager `id = 2` at any level of the hierarchy.
Write a query to return every category's name, depth level, and number of products assigned directly to that category.
Write a query to return the ID, name, and step number for every person in the chain. Employee `id = 28` is at step `1`, their direct manager at step `2`, and so on up to the CEO.
Write a query to return the ID, name, and depth level for employee `id = 2` and every employee who reports to them at any level.
Write a query to return every qualifying depth level and its employee count.
These problems are part of the Recursive CTEs lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.