Interview Questions/SQL/Employee Hierarchy Depth (Recursive CTE)

Employee Hierarchy Depth (Recursive CTE)

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

Medium

Employee Hierarchy Depth (Recursive CTE)

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)

Examples

Example 1

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.

query.sql