Org Chart Depth

Problem

Schema: Employee(id INT, name VARCHAR, manager_id INT). Top-of-tree employees have manager_id IS NULL. Return each employee's name and depth (root = 0).

Constraints

• Tree has no cycles.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH RECURSIVE chart AS (
  SELECT id, name, 0 AS depth
  FROM Employee
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, c.depth + 1
  FROM Employee e
  JOIN chart c ON e.manager_id = c.id
)
SELECT name, depth
FROM chart
ORDER BY depth, name;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.