Span of control: who really runs the org?
Nordvik Global has a deep reporting tree: the CEO, vice presidents, directors, managers and individual contributors. Management wants to know each leader's full span, not just direct reports: everyone who reports to them directly or through any number of levels below. Only active employees (termination_date empty) are part of anyone's span.
Task: with a recursive CTE return one row per person who has at least one active report: manager (name), direct_reports, total_reports (direct and indirect) and levels_below (how many reporting levels sit under the manager, 1 if they only have direct reports). Sort by total_reports from high to low, then manager A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| dept_id INTEGER | name TEXT |
|---|---|
| 1 | Engineering |
| 2 | Sales |
| 3 | Finance |
| 4 | People & Culture |
| 5 | Marketing |
| 6 | Operations |
Showing the first 6 of 7 rows.
| emp_id INTEGER | name TEXT | dept_id INTEGER | job_title TEXT | country TEXT | grade INTEGER | salary_usd INTEGER | hire_date TEXT | termination_date TEXT | manager_id INTEGER |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Harry Clarke | 7 | Chief Executive Officer | United Kingdom | 9 | 375000 | 2016-03-01 | NULL | NULL |
| 2 | Khalid Al Hashimi | 1 | VP of Engineering | United Arab Emirates | 8 | 237000 | 2021-03-01 | NULL | 1 |
| 3 | Zainab Ahmed | 1 | Director of Engineering 1 | Pakistan | 7 | 59000 | 2021-10-12 | NULL | 2 |
| 4 | Hina Khan | 1 | Engineering Manager | Pakistan | 6 | 40000 | 2024-08-10 | NULL | 3 |
| 5 | Ren Tanaka | 1 | QA Engineer | Japan | 4 | 68000 | 2022-12-19 | NULL | 4 |
| 6 | Piotr Zielinski | 1 | Data Engineer | Poland | 3 | 33000 | 2025-10-23 | NULL | 4 |
Showing the first 6 of 58 rows.
| review_id INTEGER | emp_id INTEGER | review_period TEXT | rating INTEGER | reviewed_on TEXT |
|---|---|---|---|---|
| 1 | 2 | 2024-H2 | 2 | 2024-12-20 |
| 2 | 2 | 2025-H1 | 1 | 2025-06-18 |
| 3 | 2 | 2025-H2 | 3 | 2025-12-30 |
| 4 | 2 | 2026-H1 | 2 | 2026-06-25 |
| 5 | 3 | 2024-H2 | 3 | 2024-12-29 |
| 6 | 3 | 2025-H1 | 4 | 2025-06-18 |
Showing the first 6 of 178 rows.
| promo_id INTEGER | emp_id INTEGER | promo_date TEXT | old_grade INTEGER | new_grade INTEGER | old_salary_usd INTEGER | new_salary_usd INTEGER |
|---|---|---|---|---|---|---|
| 1 | 3 | 2025-11-26 | 6 | 7 | 50000 | 59000 |
| 2 | 4 | 2025-12-10 | 5 | 6 | 33000 | 40000 |
| 3 | 6 | 2026-05-08 | 2 | 3 | 28000 | 33000 |
| 4 | 7 | 2025-09-14 | 6 | 7 | 120000 | 143000 |
| 5 | 12 | 2025-01-06 | 2 | 3 | 27000 | 29000 |
| 6 | 29 | 2026-02-23 | 6 | 7 | 80000 | 91000 |
Showing the first 6 of 9 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Seed the recursion with (manager, direct report, depth 1), then keep joining the reports of the last person found while carrying the original manager along.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.