How does a recursive CTE work, and how would you use one to walk an employee-manager hierarchy?

How to think about it

The thing interviewers want to hear is that you understand the engine’s loop, not just the syntax. A recursive CTE isn’t magic — the database literally runs the recursive member over and over, each time joining it against only the newly produced rows from the previous pass, until a pass produces nothing new.

The two-part structure

WITH RECURSIVE org_tree AS (

    -- Anchor member: seed rows (the root of the tree)
    SELECT employee_id, name, manager_id,
           0 AS depth,
           name AS path
    FROM employees
    WHERE manager_id IS NULL     -- CEO has no manager

    UNION ALL

    -- Recursive member: extend one level at a time
    SELECT e.employee_id, e.name, e.manager_id,
           ot.depth + 1,
           ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, depth, path
FROM org_tree
ORDER BY path;

What the engine actually does, pass by pass:

Pass	Working table contains	Recursive member finds
0 (anchor)	CEO (depth 0)	—
1	CEO	Direct reports of CEO (depth 1)
2	CEO + depth-1 rows	Reports of depth-1 rows (depth 2)
…	…	… until no new rows

What the iteration looks like

Anchor ──► Working table ──► Recursive member
                ▲                    │
                └────────────────────┘
                  each pass appends new rows
                  until recursive member = 0 rows
                          │
                          ▼
                     Final result

Finding a specific subtree

Change the anchor’s WHERE to start from any node — everything beneath it comes along automatically:

WITH RECURSIVE subtree AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE employee_id = 3          -- start from a specific manager

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.employee_id
)
SELECT * FROM subtree ORDER BY depth, name;

Run it and see the full hierarchy, then try starting from a different employee_id:

The key insight

The recursive member can only reference the CTE’s current working set, not the accumulated result. That’s what makes each pass extend exactly one level — not the whole tree again. The loop terminates naturally when the join finds no new children.