Build each employee's reporting chain to the CEO

Question

An access-review tool must display the full escalation chain for each person.

`org_members` stores `reports_to` as a self-reference; the CEO's is `NULL`. Using a recursive CTE, return `member_id`, `member_name`, `levels_from_top` (0 for the CEO) and `chain` — the names from the CEO down to that mem

Accepted Answer

Step 1: Anchor on the top of the hierarchy: the row where `reports_to IS NULL` starts at level 0 with a chain containing only its own name. Step 2: In the recursive member, join `org_members` to the CTE on `c.member_id = m.reports_to` so each pass picks up the direct reports of everyone found so far. Step 3: Increment `levels_from_top` by 1 per hop and append the name to `chain` with the `||` string concatenation operator. Step 4: `UNION ALL` (not `UNION`) keeps the recursion cheap and preserves every distinct path; Postgres stops when an iteration adds no rows. Step 5: Select all four columns from the CTE. Every member appears exactly once because each has at most one parent.

Build each employee's reporting chain to the CEO

More Recursive CTEs questions

Asked at Target