We use analytics and advertising cookies to understand how the site is used and whether our ads on Facebook and Instagram work. They are set only if you accept. See our Privacy Policy for details.
This problem is paper-only - see explanation for the canonical SQL solution.
Given Employee(id, manager_id) where manager_id is null for the CEO, compute each employee's depth below the CEO (CEO depth = 0). The org is a forest in pathological cases; treat each null-manager root as depth 0.
For the auto-grader: return [{ id, depth }] sorted by id ascending. Cycles should not exist; if they do, exclude affected nodes.
A hard sql problem, graded against 8 test cases (6 of them hidden).
Query-writing problems - joins, aggregation, window functions, and query tuning.
Reach for it when you see: A schema and a question about the data rather than a function signature.
More SQL problems →```sql
WITH RECURSIVE org AS (
SELECT id, 0 AS depth
FROM Employee
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, o.depth + 1
FROM Employee e
JOIN org o ON e.manager_id = o.id
)
SELECT id, depth
FROM org
ORDER BY id;
```
Anatomy of a recursive CTE:
1. Anchor query - non-recursive base case (the CEO).
2. Recursive step - references the CTE itself, must produce strictly smaller / new rows.
3. UNION ALL - combines.
Cycle protection: if data may contain cycles, track a path array (`array_append(o.path, e.id)`) and add `AND e.id <> ALL(o.path)` in the recursive step. Most engines also enforce a `MAX RECURSION` (default 100 in SQL Server, 1000 in MySQL).
The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.
Read off this problem's own test suite, so these are the cases a submission actually has to survive.
These apply to the pattern as a whole, not just this problem.