Helix Systems' access control system needs to identify every employee who falls within the reporting chain of manager id = 2, including every level of indirect reports.
Write a query to return the ID and name of every employee who reports to manager id = 2 at any level of the hierarchy.
Assumptions:
- The
employeestable has one row per employee with anid, aname, and amanager_id. - An employee is a direct report of manager
id = 2if theirmanager_idequals2. An employee is an indirect report if their reporting chain leads back to managerid = 2through one or more intermediate managers. - Both direct and indirect reports of manager
id = 2should appear. Managerid = 2themselves should not appear in the result.
Output:
- One row per qualifying employee, with columns
idandname.
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
reports AS (
SELECT
id,
name
FROM
employees
WHERE
manager_id = 2
UNION ALL
SELECT
e.id,
e.name
FROM
employees e
JOIN reports r ON e.manager_id = r.id
)
SELECT
id,
name
FROM
reportsThe shape
The anchor seeds the recursion with the direct reports of manager id = 2, not with the manager themselves. The recursive member then walks downward from those seeds, adding each level of indirect reports. Manager id = 2 never enters the CTE, which is exactly what the prompt requires.
Clause by clause
- The anchor selects every direct report of manager
id = 2:
SELECT id, name
FROM employees
WHERE manager_id = 2The filter manager_id = 2 keeps only the rows whose recorded manager is employee 2. Anna Kim, Ben Walsh, Cara Lee, and Ora Price are the four direct reports; they are the seed set of reports.
- The recursive member adds each subsequent level by linking on
manager_id = id:
UNION ALL
SELECT e.id, e.name
FROM employees e
JOIN reports r ON e.manager_id = r.idPass one matches every employee whose manager_id is one of the four direct-report ids and adds them to reports — Sam Torres, Tara Upton, and the rest of the depth-three layer under manager 2. Pass two would look for employees whose manager_id matches one of those newly-added rows, and on this data the subtree terminates there.
- The main query returns the accumulated set:
SELECT id, name
FROM reportsFifteen employees in total — the four direct reports plus the eleven indirect reports beneath them. Manager 2 is not in the result, because the anchor never seeded their row.
Why anchor on the direct reports and not on the manager
Seeding the anchor with WHERE id = 2 and then recursing on manager_id = id would include manager 2 in the CTE at depth 1 — fine for a "the manager and everyone beneath" question, but wrong here, where the prompt explicitly excludes the manager. The cleaner fix is to push the exclusion into the anchor: seed with the direct reports (manager_id = 2) and let the recursion handle the rest. The shape of the anchor is what defines who is in the result set.
The trap
The two choices — anchor on the manager or anchor on the direct reports — produce result sets that differ by exactly one row. Either spelling can be right depending on the prompt's wording about whether the root is included. The prompt here calls for "every employee who falls within the reporting chain," which excludes the manager themselves. Reading the inclusion rule off the prompt and writing the anchor to match is the structural step; the recursive member's shape stays the same either way.
You practiced anchoring a WITH RECURSIVE on the direct reports of a specific manager — the anchor restricts the seed set; the recursion expands it down through the subtree without re-including the manager.
