SQL Senior (5–8 yrs) Coding Frequent SQL recursive CTE hierarchical data WITH RECURSIVE PostgreSQL

Write a recursive CTE to traverse a hierarchical parent-child table.

sql-sen-002

Your answer

Answer as you would in a real interview — explain your thinking, not just the conclusion.

Model answer

A recursive CTE consists of two parts separated by UNION ALL: the anchor member that seeds the recursion with the root rows, and the recursive member that joins the CTE back to the table to find children. The database executes the recursive member repeatedly until it returns no new rows. I always add a depth counter and a cycle guard (or CYCLE clause in PostgreSQL 14+) to prevent infinite loops in case the data contains a cycle. For large hierarchies I also add a materialized path or a closure table as a precomputed alternative, because the recursive CTE can be expensive at depth.

Code example

-- employees(id, name, manager_id)
WITH RECURSIVE org_chart AS (
    -- Anchor: start from CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth, ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || e.id
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE NOT e.id = ANY(oc.path) -- cycle guard
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    depth
FROM org_chart
ORDER BY path;

Follow-up

How would you detect and break a cycle in the hierarchy? What is a closure table and when is it more efficient than a recursive CTE?