Interview Drills · WITH RECURSIVE · hierarchies
45. Org chart with recursive CTE
Concept: WITH RECURSIVE · hierarchiesshow
WITH RECURSIVE walks a hierarchy of unknown depth. It has two parts joined by UNION ALL: an anchor query for the starting rows (the top of the tree), and a recursive query that joins the table to the rows found so far to get the next level. It repeats until a step finds no new rows.
Example (different data):
WITH RECURSIVE tree AS ( SELECT id, name, 1 AS depth FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, t.depth + 1 FROM folders f JOIN tree t ON f.parent_id = t.id ) SELECT * FROM tree;
Your task
Classic interview question: HR's org chart needs every employee's level and full reporting line, however deep it goes.
Return name, level (0 for employees with no manager, 1 for their direct reports, and so on) and chain (names from the top manager down to the employee, joined with ' > ', e.g. 'Omar > Tomás'). Sort by chain.
Starting Postgres in your browser…
Checking your access…