Helix Systems' workforce planning tool analyzes how headcount is distributed across the levels of the organizational hierarchy.
Write a query to return every depth level and the number of employees at that level.
Assumptions:
- The
employeestable has one row per employee with anidand amanager_id. - The CEO has a missing
manager_idand sits at depth1. Employees one step below the CEO sit at depth2, and so on. - Each depth level with at least one employee should appear once. The headcount at each level is the number of employees whose computed depth equals that level.
Output:
- One row per depth level, with columns
depthandemployee_count.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH RECURSIVE
org AS (
SELECT
id,
1 AS depth
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.id,
o.depth + 1
FROM
employees e
JOIN org o ON e.manager_id = o.id
)
SELECT
depth,
COUNT(id) AS employee_count
FROM
org
GROUP BY
depthThe shape
The recursive CTE produces one row per employee carrying their computed depth, and the outer GROUP BY depth then counts the rows at each level. The traversal is the same root-to-leaves walk used to assign depths; the aggregation is a follow-up step over its output.
Clause by clause
- The anchor seeds the CEO at depth
1:
SELECT id, 1 AS depth
FROM employees
WHERE manager_id IS NULLOnly id and depth are carried through the recursion, since the final aggregation only needs the count per level. Dropping name and manager_id from the projection is a small efficiency.
- The recursive member adds each subsequent level:
UNION ALL
SELECT e.id, o.depth + 1
FROM employees e
JOIN org o ON e.manager_id = o.idThe join condition is the same parent-to-child link as the unbounded tree walk. Each pass stamps the newly-added rows with one more than the source depth. The recursion ends when no further employee can be joined to a row already in org.
- The main query aggregates over the result of the recursion:
SELECT depth, COUNT(id) AS employee_count
FROM org
GROUP BY depthGROUP BY depth partitions org's rows into one bucket per level, and COUNT(id) counts the rows in each bucket. Depth 1 has one employee (the CEO), depth 2 has nine, depth 3 has nineteen, depth 4 has thirty-one.
Why an aggregation outside the recursion and not inside
The recursive member cannot use an aggregate over the CTE: an aggregate would collapse the rows the next pass needs to join against, and the recursion would lose its per-row structure. The right shape for "compute X per row recursively, then summarise" is two stages: let the recursion produce one row per record with the per-record value attached, then aggregate that flat result with a standard GROUP BY in the main query.
The trap
The depth column inside the recursion is a per-row attribute, not an aggregate. COUNT(id) in the outer query counts rows whose depth matches; it does not count distinct depth values or sum depths. Confusing the role of depth (a partition key in the outer GROUP BY) with the role it plays inside the recursion (a running counter on each row) is the easiest reading mistake on a query that mixes recursion and aggregation. The two-stage layering keeps each role legible.
You practiced an aggregate over the output of a WITH RECURSIVE traversal — recurse first to compute depth per record, then group by depth in the main query for a level-wise headcount.
