Helix Systems' HR platform needs a complete view of the organizational hierarchy.
Write a query to return the ID, name, and depth level for every employee.
Assumptions:
- The CEO has a missing
manager_id. Every other employee has amanager_idreferencing another employee'sid. - The CEO is at depth
1, employees who report directly to the CEO are at depth2, employees who report to a depth-2employee are at depth3, and so on through every reporting level. - Every employee appears in the result exactly once.
Output:
- One row per employee, with columns
id,name, anddepth.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH RECURSIVE
org AS (
SELECT
id,
name,
manager_id,
1 AS depth
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
o.depth + 1
FROM
employees e
JOIN org o ON e.manager_id = o.id
)
SELECT
id,
name,
depth
FROM
orgThe shape
The recursion has no depth filter, so it keeps adding levels until no employee remains whose manager_id matches a row already in the CTE. On a tree-shaped reporting structure that has no cycles, the recursion terminates naturally once every leaf has been added.
Clause by clause
- The anchor seeds the CEO at depth
1:
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULLSarah Chen is the only row with a missing manager_id. She enters org carrying depth = 1.
- The recursive member adds every employee whose manager is already in
org:
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e
JOIN org o ON e.manager_id = o.idPass one matches every employee whose manager_id = 1 and stamps them with depth = 2 — Marcus, Priya, James, Lisa, and the rest of the direct reports. Pass two matches every employee whose manager_id is one of those depth-2 ids and stamps them with depth = 3. Pass three reaches depth 4. Pass four finds no rows whose manager_id matches any of the depth-4 ids, and the recursion stops.
- The final
SELECTreads the accumulated hierarchy:
SELECT id, name, depth
FROM orgAll sixty employees are in org by now: one at depth 1, nine at depth 2, nineteen at depth 3, and thirty-one at depth 4.
Why no depth limit
A depth limit is the defense against cycles or against runaway traversal on unbounded graphs. A reporting hierarchy with a single root and no employee reporting to themselves directly or indirectly is a tree — every employee is reachable in exactly one path from the CEO, and the recursion converges on its own. Adding WHERE o.depth < N here would either be a no-op (if N is at or above the tree's true depth) or would silently truncate the result (if N is too low). For a clean tree, natural termination is the right behavior.
The trap
Natural termination assumes the reporting graph is acyclic. If a single employee's manager_id were edited to point at one of their own subordinates, the recursive join would match forever and the query would run until PostgreSQL killed it on a resource limit. The price of dropping the depth filter is the assumption that the data is clean. In production code against editable HR data, a defensive depth cap (WHERE o.depth < 20 or similar) is the standard belt-and-braces guard even when the data is expected to be a tree.
You practiced unbounded WITH RECURSIVE traversal — recurse until no new rows can be added; when the data is a clean tree, the recursion terminates naturally at the leaves.
