Chain of Command
manager_id is the id of a person's manager, a row of the same table, and NULL for a person at the top. Return every person's name and depth: the number of managers above them, following the chain to the top. Someone at the top has depth 0, their direct reports 1, and so on. Rows may come in any order.
Example 1
employees
| id | name | manager_id |
|---|---|---|
| 1 | Ada | NULL |
| 2 | Ben | 1 |
| 3 | Cy | 2 |
| 4 | Dee | 1 |
| 5 | Eli | NULL |
returns
| name | depth |
|---|---|
| Ada | 0 |
| Ben | 1 |
| Cy | 2 |
| Dee | 1 |
| Eli | 0 |
Constraints
- Up to 12 people
- Names are unique
- Every chain ends at someone with no manager
Write it in your browser, against the clock. The model query, with a note on each piece of it, comes after.
More SQL problems
- Daily BarsHard
- Longest Winning StreakHard
- Median SalaryHard
- Month Over MonthHard
- Moving AverageHard
- Position Over TimeHard
- SessionsHard
- Three Days RunningHard