Hierarchy report

Write an org-chart report with a recursive CTE.

Hierarchy report

In this challenge you will extract an org hierarchy with a recursive CTE.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY level;

Organization tree.

WITH RECURSIVE defines a self-referencing temporary result set (CTE) named org; the query after it uses it like a table. All columns are selected from org. The result is sorted by level in ascending order.

Exercise Hierarchy report

Extract the org hierarchy with a recursive CTE: anchor the root, recursively add subordinates.

Show example solution

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY level;

Test yourself

What joins in a recursive CTE?
- UNION ALL
- INTERSECT
- JOIN
- EXCEPT
Show answer

UNION ALL — A recursive CTE joins with UNION ALL.
Hierarchy report: match the terms

recursive

recursive
anchor

base
UNION ALL

combine
level

depth

Next lesson: Window analysis

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies