Total Team Size and Payroll Under Each Manager — SQL Problem
For budgeting, every people manager is accountable for their whole subtree: direct reports, their reports, and so on down.
- Difficulty: Hard
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #242
Problem statement
For budgeting, every people manager is accountable for their whole subtree: direct reports, their reports, and so on down. The organisation may have more than one top-level leader (manager_id NULL), for example after an acquisition.
For every employee with at least one direct report, return emp_id, full_name, team_size (the number of people anywhere below them) and team_ctc (the sum of those people's annual_ctc; the manager's own CTC is not included). Order by team_size descending, then emp_id ascending.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| manager_id | int |
| annual_ctc | int |
Primary key: emp_id.
manager_id is the emp_id of the direct manager, NULL for a top-level leader. Reporting lines never loop.
Examples
Example 1
Employee
| emp_id | full_name | manager_id | annual_ctc |
|---|---|---|---|
| 1 | Meera Iyer | NULL | 9000000 |
| 2 | Arjun Rao | 1 | 4500000 |
| 3 | Pooja Shah | 1 | 4200000 |
| 4 | Karan Nair | 2 | 2400000 |
| 5 | Sneha Das | 4 | 1600000 |
| 6 | Rahul Khan | 3 | 1800000 |
| 7 | Tanvi Bose | 4 | 1200000 |
| 8 | Liam Joshi | NULL | 6000000 |
| 9 | Emma Patel | 8 | 1500000 |
Output
| emp_id | full_name | team_size | team_ctc |
|---|---|---|---|
| 1 | Meera Iyer | 6 | 15700000 |
| 2 | Arjun Rao | 3 | 5200000 |
| 4 | Karan Nair | 2 | 2800000 |
| 3 | Pooja Shah | 1 | 1800000 |
| 8 | Liam Joshi | 1 | 1500000 |
How to solve Total Team Size and Payroll Under Each Manager
The question is about everyone below a manager, at any depth, so we need the transitive closure of manager_id: one row per (boss, report) pair where the report is somewhere in the boss's subtree.
A recursive CTE builds it. The anchor is the direct pairs (manager_id, emp_id). The recursive member takes a pair (boss, x) and joins Employee on e.manager_id = x, producing (boss, x's direct report); repeating this reaches every level. Since the org chart is a forest without loops, each pair is produced exactly once and the recursion ends after as many passes as the deepest chain.
With the pairs in hand the rest is plain aggregation: group by the boss, COUNT(*) the reports and SUM their CTC (joining Employee for the report's salary — the boss's own CTC never enters, because the boss is never their own report). Employees with no direct report never appear as a boss, so they are excluded, and several top-level leaders are handled naturally.
The alternative walks upwards: from each employee, the anchor is (employee, manager) and every step replaces the boss by the boss's manager. It yields the same pair set from the other end. Both are O(n × depth).
Reference solution (MySQL)
WITH RECURSIVE reports AS (
SELECT manager_id AS boss_id, emp_id AS report_id
FROM Employee
WHERE manager_id IS NOT NULL
UNION ALL
SELECT r.boss_id, e.emp_id
FROM reports r
JOIN Employee e ON e.manager_id = r.report_id
)
SELECT b.emp_id, b.full_name, COUNT(*) AS team_size, SUM(p.annual_ctc) AS team_ctc
FROM reports r
JOIN Employee b ON b.emp_id = r.boss_id
JOIN Employee p ON p.emp_id = r.report_id
GROUP BY b.emp_id, b.full_name
ORDER BY team_size DESC, b.emp_idAnother way
WITH RECURSIVE ancestors AS (
SELECT emp_id AS report_id, manager_id AS boss_id FROM Employee WHERE manager_id IS NOT NULL
UNION ALL
SELECT a.report_id, m.manager_id
FROM ancestors a JOIN Employee m ON m.emp_id = a.boss_id
WHERE m.manager_id IS NOT NULL
)
SELECT b.emp_id, b.full_name, t.team_size, t.team_ctc
FROM (SELECT a.boss_id, COUNT(*) AS team_size, SUM(e.annual_ctc) AS team_ctc
FROM ancestors a JOIN Employee e ON e.emp_id = a.report_id GROUP BY a.boss_id) t
JOIN Employee b ON b.emp_id = t.boss_id
ORDER BY t.team_size DESC, b.emp_id← Org Chart Depth and Business Head of Every Employee · Overlapping Approved Leave Within a Team →