SQL interview question: recursive employee hierarchy
Problem
Shared data rulesFor each employee in couriers, find their level and reporting line — from the employee up to the top.
Example
couriers| courier_id | role | manager_id | fired_at | Row note |
|---|---|---|---|---|
| 1 | head_of_logistics | NULL | NULL | |
| 2 | city_lead | 1 | NULL | |
| 4 | supervisor | 2 | 2026-08-14 | ← dismissed |
| 5 | supervisor | 2 | NULL | |
| 57 | courier | 4 | NULL | |
| 58 | courier | 5 | 2026-09-03 | ← dismissed |
- Courier 4: dismissed
- Courier 58: dismissed
| courier_id | depth | chain |
|---|---|---|
| 1 | 0 | 1 |
| 2 | 1 | 2 > 1 |
| 4 | 2 | 4 > 2 > 1 |
| 5 | 2 | 5 > 2 > 1 |
| 57 | 3 | 57 > 4 > 2 > 1 |
| 58 | 3 | 58 > 5 > 2 > 1 |
Courier 57’s line passes through supervisor 4: '57 > 4 > 2 > 1', level 3. The top, 1, has the line '1'.
Output columns
depth- how many managers are above the employee; 0 for the top
chain- IDs from the bottom up joined by “ > ”
Notes
- Dismissed employees stay in the structure: their reports’ lines pass through them.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Customers who bought every categoryMedium · Relational division
- SQL: A/B test conversion rate by platformMedium · A/B tests
- SQL: 30-day customer lifetime valueHard · Product metrics
- SQL: Products that make 80% of revenueMedium · Running total
- SQL: Second-highest price in each groupEasy · Nth-highest value