How do recursive CTEs work? | Interview Question

Q: How do recursive CTEs work?

**Target Audience:** Anyone who can write a JOIN and a basic CTE, meeting `WITH RECURSIVE` for the first time; and candidates who want to explain the execution model precisely. **Difficulty:** Hard **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Picture a family tree drawn on index cards. Each card has a name and, in the corner, the name of the parent. Someone asks: *"List everyone descended from Grandma."* You cannot do it in one look. You pull Grandma's card (that is your starting point). Then

The problem

Question presented to candidate: "Walk me through how a recursive CTE actually executes. What are the two parts, how does the database know when to stop, and what would you use one for?"

What a strong answer should cover:

The shape: WITH RECURSIVE name AS (anchor UNION [ALL] recursive term); the anchor runs once, the recursive term refers to the CTE itself
The execution model: a working table holds only the rows from the previous step; the recursive term runs against it, new rows are appended to the result and become the next working table; it stops when a step produces no rows
Termination is the author's job: a WHERE condition, a join that runs out of children, UNION instead of UNION ALL, or a depth guard; engines differ on safety nets (SQL Server MAXRECURSION 100, MySQL cte_max_recursion_depth 1000, none in PostgreSQL or SQLite)
Typical uses: walking trees and graphs (org charts, categories, bill of materials), generating series and calendars
Restrictions: no aggregates in the recursive term, the CTE may be referenced only once there, column types must match (PostgreSQL rejects varchar(20) vs varchar)
Engine detail: PostgreSQL and MySQL require the RECURSIVE keyword, SQLite accepts plain WITH, SQL Server has no RECURSIVE keyword at all

Code / implementation expected: Yes — a number generator and a tree walk (descendants or ancestors) with a depth column.

Schema used by the examples:

recursive-cte

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Anyone who can write a JOIN and a basic CTE, meeting WITH RECURSIVE for the first time; and candidates who want to explain the execution model precisely. Difficulty: Hard Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Picture a family tree drawn on index cards. Each card has a name and, in the corner, the name of the parent. Someone asks: *"List everyone descended from G

The solution is hidden so you can try first

11 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe smallest recursive CTE: count from 1 to 5

SQLWalk down a tree: every category under Electronics with its depth and path

SQLWalk up a tree: the ancestors of Laptops (a breadcrumb)

PostgreSQL plan: Recursive Union over a WorkTable Scan

EXPLAIN (COSTS OFF)
WITH RECURSIVE subtree(id, name, depth) AS (
  SELECT id, name, 0 FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.name, s.depth + 1
  FROM categories AS c
  JOIN subtree AS s ON c.parent_id = s.id
)
SELECT * FROM subtree;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.