Org Chart Depth With a Recursive CTE

SQLRecursive CTESeniorStaffUpdated Nov 10, 2026

Given a CEO with manager_id IS NULL, return every employee in the org along with their depth (CEO = 0, direct reports = 1, etc.).

Schema

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree;

A recursive CTE with the RECURSIVE keyword anchors on the CEO and walks down one level per iteration. (c) omits RECURSIVE, so Postgres treats 'tree' inside the second SELECT as an unknown table and the query fails. (a) caps depth at 1. (d) just counts how many managers a person has (0 or 1), which is not depth.

Going Deeper

Postgres requires the RECURSIVE keyword on the CTE even though only one branch is recursive. Always end the recursive branch with a guaranteed terminating join condition to avoid infinite loops on cyclic data.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT e.id, e.name, 0 AS depth
FROM employees e
WHERE e.manager_id IS NULL
UNION ALL
SELECT e.id, e.name, 1
FROM employees e
WHERE e.manager_id IS NOT NULL;

Option (c)

WITH tree AS (
  SELECT id, name, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1 FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree;

Option (d)

SELECT e.id, e.name, COUNT(m.id) AS depth
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
GROUP BY e.id, e.name;