Solving tips
- A recursive CTE has two parts joined by `UNION ALL`: an anchor member (the starting rows) and a recursive member that references the CTE by name to walk one level further each iteration.
- For an org chart, anchor on the top employee (`manager_id IS NULL`) at level 1, then join employees to their manager's already-computed row and add 1 to the level.
- Recursion stops naturally when the recursive member returns no new rows; a genuine cycle in the data would loop forever unless you guard against it.
Given a self-referencing employee table where each person points to their manager, compute the depth of every employee in the org chart. The top of the org is at level 1.
Schema
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
manager_id integer REFERENCES employees(id) -- NULL for the top of the org
);
Sample data — employees:
| id | name | manager_id |
|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | Dave | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
| 7 | Grace | 4 |
Task
Return each employee’s name and their level in the hierarchy, where the top employee (no manager) is level 1, their direct reports are level 2, and so on.
Order by level ascending, then by name ascending. Expected columns: name, level.
Expected output
Alice is the top (level 1). Bob and Carol report to Alice (level 2). Dave and Eve report to Bob, and Frank reports to Carol (level 3). Grace reports to Dave (level 4).
| name | level |
|---|
| Alice | 1 |
| Bob | 2 |
| Carol | 2 |
| Dave | 3 |
| Eve | 3 |
| Frank | 3 |
| Grace | 4 |
Approach
Walk the management chain top-down with a recursive CTE. The anchor member selects the root of the org — the employee with manager_id IS NULL — and stamps it level 1. The recursive member joins each employee to the row of their manager that the CTE has already produced, carrying level + 1. Each iteration reaches exactly one level deeper, and recursion halts when no employee’s manager was emitted in the previous step.
Query
WITH RECURSIVE org AS (
-- anchor: the top of the org chart
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive: everyone reporting to a row already in `org`
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT name, level
FROM org
ORDER BY level, name;
Walkthrough
The anchor emits Alice at level 1. Iteration 1 joins employees whose manager_id = 1 (Alice’s id): Bob and Carol, at level 2. Iteration 2 joins employees reporting to Bob or Carol: Dave and Eve (manager 2) and Frank (manager 3), all at level 3. Iteration 3 finds Grace, who reports to Dave (id 4), at level 4. Iteration 4 finds no one reporting to Grace, so the recursion stops. Ordering by level then name produces Alice, Bob, Carol, Dave, Eve, Frank, Grace.
Complexity & notes
Each employee is visited once, so the work is linear in the number of rows, with recursion depth equal to the height of the tree; an index on manager_id makes each level’s join efficient. Common pitfalls: the anchor’s level column type fixes the type for the whole recursion, so start it as an integer literal; and a cycle in the data (A manages B who manages A) makes a plain UNION ALL recurse forever — guard it by carrying a path array and adding WHERE e.id <> ALL(o.path), or use UNION if you only need distinct rows. A frequent extension is to also build a readable path, e.g. add o.path || '/' || e.name to trace each employee back to the root.