Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Headcount at each level of the org · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Headcount at each level of the org

A reorg conversation needs the shape of the org: how many people sit at each level, from the CEO down. Nobody has recorded a level anywhere — the only structure in the table is manager_id. Return one row per level with the number of people at it. The CEO is level 1. Order by depth.

Result columns · in this order

`depth`	Level in the org, where the CEO is 1.
`people`	How many employees sit at that level.

How to approach it

Produce (employee_id, depth) for everyone with a recursive walk, then GROUP BY depth outside it.

Sample input

employeesOne row per employee. `manager_id` points at another row in the same table and is NULL for exactly one person, the CEO. The tree is seven levels deep in Engineering and four in Sales.

employee_id	full_name	manager_id	title	department
1	Ada Okafor	null	CEO	Executive
2	Bo Lindqvist	1	VP Engineering	Engineering
3	Chen Wei	1	VP Data	Data
4	Dara O'Neill	1	VP Sales	Sales
5	Eve Marsh	2	Director, Platform	Engineering
6	Farid Haddad	2	Director, Product Eng	Engineering
7	Gita Rao	3	Director, Analytics	Data
8	Hugo Silva	4	Director, EMEA	Sales
9	Ivy Chen	5	Staff Engineer	Engineering
10	Jonas Weber	5	Engineering Manager	Engineering
11	Kira Novak	6	Engineering Manager	Engineering
12	Liam Byrne	7	Analytics Manager	Data
13	Mina Patel	7	Data Engineer	Data
14	Noor Aziz	8	Account Executive	Sales
15	Omar Diaz	10	Senior Engineer	Engineering
16	Priya Nair	10	Engineer	Engineering
17	Quinn Doyle	11	Engineer	Engineering
18	Rosa Lima	12	Analyst	Data
19	Sam Okoro	12	Analyst	Data
20	Tara Singh	15	Engineer	Engineering
21	Uzo Eze	15	Engineer	Engineering
22	Vik Sharma	20	Junior Engineer	Engineering

22 rows — scroll inside the table to see them all.

Expected output

The exact result your query should return for this input.

depth	people
1	1
2	3
3	4
4	6
5	5
6	2
7	1

7 rows — all rows shown.

Constraints

1The level must be derived by walking the tree. Do not infer it from title or department — one staff engineer sits at the same level as two managers, and Sales is three levels shallower than Engineering.
2Every employee must be counted exactly once, including the CEO, and including people with no reports.
3Aggregate in the outer query. Grouping inside a recursive CTE is not allowed on most engines, and where it is, it changes what the recursion means.
4The result should show the org getting wider and then narrowing again — that shape is the answer the conversation is after.
5Order by depth.

Worked example

Level 1 is the CEO alone. Level 2 is the three VPs. Level 3 is the four directors, and level 4 has six people — the widest layer in the company. Then it narrows: five at level 5, two at level 6, and one at level 7. That single row at the bottom is Vik, seven levels from the CEO in a company of twenty-two people. Seven rows out of twenty-two employees, and the counts must sum back to 22. If they sum to 21, the anchor forgot the CEO; if a level is missing entirely, the walk stopped early.

What this tests

Deriving a value that exists nowhere in the table, and knowing that the aggregation belongs outside the recursive CTE rather than inside it.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules