Employee Hierarchy Depth (Recursive CTE)

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given an Employees table where each employee may have a manager. The CEO has no manager (manager_id is NULL). Write a SQL query using a recursive CTE to compute the hierarchy depth of every employee. The CEO has depth 0, direct reports have depth 1, their reports have depth 2, and so on. Return id, name, and depth for all employees, ordered by id. Table: Employees

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
manager_id	INT	ID of the manager (NULL for the root)

Database Schema (Inferred)

Employees

Column Name	Example Value
id	1
name	CEO
manager_id	NULL

Example

Employees

id	name	manager_id
1	CEO	NULL
2	VP1	1
3	VP2	1
4	Mgr1	2
5	Mgr2	2
6	Mgr3	3
7	Emp1	4
8	Emp2	4
9	Emp3	6

Output

id	name	depth
1	CEO	0
2	VP1	1
3	VP2	1
4	Mgr1	2
5	Mgr2	2
6	Mgr3	2
7	Emp1	3
8	Emp2	3
9	Emp3	3

Explanation:

Start with the root (manager_id IS NULL) at depth 0. Each recursive step increases depth by 1 for all direct reports.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.