How do you approach SQL: Reporting Depth Below CEO (Recursive CTE)?

Recursive CTE: anchor = CEO (manager_id IS NULL) at depth 0; recursive step joins child to parent and adds 1.

What is the optimal solution to SQL: Reporting Depth Below CEO (Recursive CTE)?

Canonical SQL (Postgres):. Anatomy of a recursive CTE: Anchor query - non-recursive base case (the CEO). Recursive step - references the CTE itself, must produce strictly smaller / new rows. 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.

What are common mistakes on SQL: Reporting Depth Below CEO (Recursive CTE)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

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.

SQL: Reporting Depth Below CEO (Recursive CTE): approach, complexity and pitfalls

A hard sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

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 SQLproblems →

If you are stuck

Show the 2-step hint ladder

Recursive CTE: anchor = CEO (manager_id IS NULL) at depth 0; recursive step joins child to parent and adds 1.
Most engines need `WITH RECURSIVE` (Postgres, MySQL 8+, SQLite). SQL Server omits the keyword.

The optimal approach

Show the approach: Canonical SQL (Postgres):

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

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.