Find All Subordinates Under a Manager (Including Herself)
Walk the org chart downward from employee_id 5 (Emma Sales Mgr). Return Emma plus every direct and indirect report. level_under_manager = 0 for Emma, 1 for direct reports, 2 for skip-level, etc. Return employee_id, employee_name, level_under_manager, manager_id — ordered by level_under_manager, employee_id.
- Recursive CTE
- Joins
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
HR operations lead A manager workspace needs the complete team beneath employee 5, including the selected manager.
Return
- Return employee identity, depth under the manager, and manager_id.
- Order by depth and employee_id.
Constraints
- Anchor on employee_id 5.
- Walk from parent to child with UNION ALL.
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
Anchor on the specific manager (employee_id = 5) at level 0.
Hint 2
To EXCLUDE Emma from the result, change the anchor to `WHERE manager_id = 5` and start at level 1.
Hint 3
Each recursive step descends one tier: e.manager_id = s.employee_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE subordinates AS (SELECT employee_id, employee_name, manager_id, 0 AS level_under_manager FROM employees WHERE employee_id = 5 UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, s.level_under_manager + 1 FROM employees e INNER JOIN subordinates s ON e.manager_id = s.employee_id) SELECT employee_id, employee_name, level_under_manager, manager_id FROM subordinates ORDER BY level_under_manager, employee_id;Why this works
Subtree walks are essential for impact analysis ("everything under this manager"), permissions ("can this user see records in this subtree?"), and rollup metrics (Q19).
Success check
The manager and every direct or indirect report appear once at the correct depth.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | employee_name | level_under_manager | manager_id |
|---|---|---|---|
| 5 | Emma Sales Mgr | 0 | 2 |
| 8 | Henry Sales Rep | 1 | 5 |
| 9 | Ivy Sales Rep | 1 | 5 |
| 13 | Mia Overpaid Jr | 1 | 5 |
| 15 | Olivia Sales Rep | 1 | 5 |
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.