Total Team Size and Payroll Under Each Manager

Hard
242.

Total Team Size and Payroll Under Each Manager

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

ColumnType
emp_idint
full_namevarchar
manager_idint
annual_ctcint

Primary key: emp_id.

manager_id is the emp_id of the direct manager, NULL for a top-level leader. Reporting lines never loop.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namemanager_idannual_ctc
1Meera IyerNULL9000000
2Arjun Rao14500000
3Pooja Shah14200000
4Karan Nair22400000
5Sneha Das41600000
6Rahul Khan31800000
7Tanvi Bose41200000
8Liam JoshiNULL6000000
9Emma Patel81500000

Output

emp_idfull_nameteam_sizeteam_ctc
1Meera Iyer615700000
2Arjun Rao35200000
4Karan Nair22800000
3Pooja Shah11800000
8Liam Joshi11500000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
full_namevarchar
manager_idint
annual_ctcint

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_idfull_namemanager_idannual_ctc
1Meera IyerNULL9000000
2Arjun Rao14500000
3Pooja Shah14200000
4Karan Nair22400000
5Sneha Das41600000
6Rahul Khan31800000
7Tanvi Bose41200000
8Liam JoshiNULL6000000
9Emma Patel81500000

Output

emp_idfull_nameteam_sizeteam_ctc
1Meera Iyer615700000
2Arjun Rao35200000
4Karan Nair22800000
3Pooja Shah11800000
8Liam Joshi11500000

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_id

Another 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 →