Helix Systems' organizational restructuring tool identifies every employee within a specific subtree of the hierarchy. The subtree is rooted at employee id = 2.
Write a query to return the ID, name, and depth level for employee id = 2 and every employee who reports to them at any level.
Assumptions:
- The
employeestable has one row per employee with anid, aname, and amanager_id. - Employee
id = 2is the root of the subtree and appears at depth1. Direct reports of employeeid = 2are at depth2, indirect reports at depth3, and so on through every level beneath them. - Every employee in the subtree (including employee
id = 2) appears once. Employees outside the subtree do not appear.
Output:
- One row per subtree member, 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
subtree AS (
SELECT
id,
name,
1 AS depth
FROM
employees
WHERE
id = 2
UNION ALL
SELECT
e.id,
e.name,
s.depth + 1
FROM
employees e
JOIN subtree s ON e.manager_id = s.id
)
SELECT
id,
name,
depth
FROM
subtreeThe shape
The anchor seeds the recursion with the subtree root itself, and the recursive member walks downward by linking children to parents already in the CTE on manager_id = id. Because the anchor's filter is WHERE id = 2 — a single row — the recursion is bounded by the subtree of that one node; employees outside Marcus Reid's branch are never reachable from any row in the CTE.
Clause by clause
- The anchor seeds the root of the subtree:
SELECT id, name, 1 AS depth
FROM employees
WHERE id = 2One row enters subtree carrying depth = 1. Marcus Reid is included in the result because the anchor pulls his row in directly.
- The recursive member walks downward through the subtree:
UNION ALL
SELECT e.id, e.name, s.depth + 1
FROM employees e
JOIN subtree s ON e.manager_id = s.idThe join e.manager_id = s.id matches each employee to a row already in subtree who is their manager. Pass one adds Anna Kim, Ben Walsh, Cara Lee, and Ora Price at depth 2 — the four employees whose manager_id is 2. Pass two adds the eleven employees who report to those four, all stamped depth = 3. Pass three finds no employees whose manager_id matches any depth-3 row in the subtree, so the recursion stops.
- The final
SELECTreturns the subtree:
SELECT id, name, depth
FROM subtreeSixteen rows: Marcus Reid at depth 1, four direct reports at depth 2, eleven indirect reports at depth 3.
Why anchor on a single node and not on direct reports
The prompt asks for the root and every descendant. Seeding the anchor with the root's row puts the root at depth 1 automatically; the recursion then handles the descendants. The alternative — seeding the anchor with the direct reports and then unioning the root in separately — produces the same result, but with an extra clause and an extra place for the depth numbering to drift out of alignment. Single-node anchor is the cleaner shape when "the root and everyone beneath" is the contract.
The trap
The subtree is bounded by the join graph, not by an explicit filter on the recursive member. Once subtree is seeded with employee 2, the only employees the recursive join can reach are those whose manager_id traces back to him through one or more steps. Employees outside Marcus's branch never enter the CTE because no row already in subtree ever matches their manager_id. This is structurally different from "compute the full hierarchy, then filter to one subtree," which materialises the whole tree first. The single-node anchor is the more efficient shape because the recursion does no work outside the subtree of interest.
You practiced a downward WITH RECURSIVE rooted at a specific node — the anchor seeds a single record (the subtree root); the recursion follows manager_id matches downward to every descendant.
