SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…