codejump
HardPro challengeSQL

Total Reports Under Each Manager

SQLDatabasesRecursive CTE

Write a query that returns, for every manager, the **total number of
people under them — direct reports and** indirect reports (their
reports' reports, and so on). Employees with nobody under them should not
appear at all.

Table: `employees`

idnamemanager_id
1CEONULL
2VP Eng1
3VP Sales1
4Eng Manager2
5Engineer A4
6Engineer B4
7Sales Rep3

Expected output (columns: ancestor_id, total_reports), ordered by
ancestor_id:

1|6
2|3
3|1
4|2

Hint — first build every (ancestor_id, descendant_id) pair the

hierarchy implies (a self-join-like recursive CTE), then GROUP BY

the ancestor to count.

Sample tests

Test #1Single chain — counts decrease by one at each level
Input: "CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);\nINSERT INTO employees VALUES\n (1, 'A', NULL),\n (2, 'B', 1),\n (3, 'C', 2),\n (4, 'D', 3);"
Output: "1|3\n2|2\n3|1"
Test #2CEO has 6 total reports across both branches
Input: "CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);\nINSERT INTO employees VALUES\n (1, 'CEO', NULL),\n (2, 'VP Eng', 1),\n (3, 'VP Sales', 1),\n (4, 'Eng Manager', 2),\n (5, 'Engineer A', 4),\n (6, 'Engineer B', 4),\n (7, 'Sales Rep', 3);"
Output: "1|6\n2|3\n3|1\n4|2"
← All challenges