Find All Managers in Reporting Chain (Path to CEO)
Walk UPWARD from employee_id 10 (Jack Engineer) to the CEO. Return the employee, then each manager, then their manager, etc. level = 1 at Jack, increasing toward the top. Return employee_id, employee_name, manager_id, level — ordered by level.
- Recursive CTE
- Joins
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Executive reporting analyst An escalation workflow needs one employee’s complete upward management chain in traversal order.
Return
- Return employee identity, manager_id, and level.
- Order by level from employee to CEO.
Constraints
- Anchor on employee_id 10.
- Reverse the recursive join to move from child to parent.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERemployee_nameTEXTmanager_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Mirror of Q7: same recursion, opposite direction. The JOIN flips: e.employee_id = mc.manager_id.
Hint 2
Recursion stops naturally at the CEO (manager_id IS NULL → no row to join).
Hint 3
Useful for "who needs to approve this?" or "who is in this employee's management chain?".
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE manager_chain AS (SELECT employee_id, employee_name, manager_id, 1 AS level FROM employees WHERE employee_id = 10 UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, mc.level + 1 FROM employees e INNER JOIN manager_chain mc ON e.employee_id = mc.manager_id) SELECT employee_id, employee_name, manager_id, level FROM manager_chain ORDER BY level;Why this works
Upward and downward walks share the recursive-CTE shape; only the join direction flips. Termination is automatic for trees — recursion stops when a row has no parent (or no children).
Success check
The employee and every ancestor through the CEO appear exactly once in chain order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | employee_name | manager_id | level |
|---|---|---|---|
| 10 | Jack Engineer | 6 | 1 |
| 6 | Frank Eng Mgr | 3 | 2 |
| 3 | Carol VP Eng | 1 | 3 |
| 1 | Alice CEO | NULL | 4 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.