Problem
HR wants to visualize the complete management hierarchy. Using the employees table (which has a self-referencing manager_id), write a recursive CTE that returns every employee with their id, name, manager_id, and depth in the hierarchy (0 = CEO), ordered by depth then name.
Schema
employees
| column | type |
|---|
| id | INTEGER |
| name | TEXT |
| manager_id | INTEGER (NULL for the root) |
Sample Data
| id | name | manager_id |
|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | Dave | 2 |
| 5 | Eve | 2 |
Expected Output
| id | name | manager_id | depth |
|---|
| 1 | Alice | NULL | 0 |
| 2 | Bob | 1 | 1 |
| 3 | Carol | 1 | 1 |
| 4 | Dave | 2 | 2 |
| 5 | Eve | 2 | 2 |