Recursive CTE for hierarchy traversal.
Starting with Mia Jackson, return her reporting chain all the way up to the person with no manager. Columns: `depth`, `name`. depth=0 is Mia herself, depth=1 is her manager, and so on. Order by depth. Table: `ci_employees`.
| Column | Type | Key |
|---|---|---|
| id | INT | PK |
| name | TEXT | |
| department_id | INT | FK → ci_departments.id |
| manager_id | INT | FK → ci_employees.id |
| salary | INT | |
| hire_date | DATE |
Each hint you reveal reduces the XP you can earn. Try the query first.
WITH RECURSIVE chain AS (anchor UNION ALL recursive_step) SELECT ...
Anchor: WHERE name = 'Mia Jackson' with depth = 0.
Recursive step: JOIN ci_employees e ON e.id = chain.manager_id, depth + 1.
Stop when manager_id is NULL — which happens automatically when the JOIN finds no match.