SQL: Reporting Depth Below CEO (Recursive CTE)

HardDatabases

Description

> 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.

Example 1

Input: employees = [{id:1,manager_id:null},{id:2,manager_id:1},{id:3,manager_id:2},{id:4,manager_id:1}]

Output: [{id:1,depth:0},{id:2,depth:1},{id:3,depth:2},{id:4,depth:1}]

Constraints

- 0 <= employees.length <= 10^4 - ids are positive integers; manager_id is either null or references an existing id.

Loading editor...