Organizational Hierarchy with Path (Recursive CTE)
Use a recursive CTE to walk the org chart from CEO downward. For each row return employee_id, employee_name, manager_id, level (CEO=1), and hierarchy_path (the reporting chain shown as 'CEO -> VP -> ... -> employee'). Order by level, employee_id.
- Recursive CTE
- CTEs
- Joins
- Subqueries
- NULL handling
Exercise brief
Understand the request
Org structure analyst A directory export needs the complete root-to-employee reporting path and depth for every connected employee.
Return
- Return IDs, names, manager_id, level, and hierarchy_path.
- Order by level and employee_id.
Constraints
- Use an anchor member for roots.
- Use UNION ALL and a recursive parent-to-child join.
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: WHERE manager_id IS NULL — the single CEO at level 1.
Hint 2
Recursive step: JOIN employees e ON e.manager_id = oh.employee_id — every iteration drops one level deeper.
Hint 3
String concat: `||` works on SQLite/Postgres; MySQL/SQL Server need CONCAT(...).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE org_hierarchy AS (SELECT employee_id, employee_name, manager_id, 1 AS level, employee_name AS hierarchy_path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, oh.level + 1, oh.hierarchy_path || ' -> ' || e.employee_name FROM employees e INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id) SELECT employee_id, employee_name, manager_id, level, hierarchy_path FROM org_hierarchy ORDER BY level, employee_id;Why this works
The recursive-CTE shape (anchor UNION ALL step) is the standard tool for arbitrary-depth tree walks. The hierarchy_path column gives you a human-readable lineage and doubles as a sort key for DFS pre-order traversal (Q23).
Success check
Every connected employee appears once with the correct depth and root-to-node path.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | employee_name | manager_id | level | hierarchy_path |
|---|---|---|---|---|
| 1 | Alice CEO | NULL | 1 | Alice CEO |
| 2 | Bob VP Sales | 1 | 2 | Alice CEO -> Bob VP Sales |
| 3 | Carol VP Eng | 1 | 2 | Alice CEO -> Carol VP Eng |
| 4 | David VP HR | 1 | 2 | Alice CEO -> David VP HR |
| 5 | Emma Sales Mgr | 2 | 3 | Alice CEO -> Bob VP Sales -> Emma Sales Mgr |
| 6 | Frank Eng Mgr | 3 | 3 | Alice CEO -> Carol VP Eng -> Frank Eng Mgr |
| 7 | Grace HR Mgr | 4 | 3 | Alice CEO -> David VP HR -> Grace HR Mgr |
| 14 | Noah Eng Lead | 3 | 3 | Alice CEO -> Carol VP Eng -> Noah Eng Lead |
| 8 | Henry Sales Rep | 5 | 4 | Alice CEO -> Bob VP Sales -> Emma Sales Mgr -> Henry Sales Rep |
| 9 | Ivy Sales Rep | 5 | 4 | Alice CEO -> Bob VP Sales -> Emma Sales Mgr -> Ivy Sales Rep |
Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.
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.