Business Problem

Meta's People Analytics team is supporting a reorg and needs, for every manager in the company, their total org size rolled all the way down the management chain (not just direct reports), how many layers deep their org goes, and the total salary cost sitting under them — so leadership can spot managers with unusually flat or unusually deep structures before finalizing the new reporting lines. The employee table only stores each person's direct manager_id, so the rollup has to walk the hierarchy itself rather than relying on a pre-computed org level field. Large organizations restructure their reporting lines periodically, and doing it well requires knowing more than just 'who reports to whom' — you need total downstream headcount, org depth, and cost rolled up through every layer of management. This is a classic recursive hierarchy problem that shows up any time HR, finance, or ops needs to analyze a self-referencing employee-manager table, whether for reorgs, compensation planning, or identifying overly narrow or overly flat management chains.

Dataset & Schema

A single self-referencing employees table with employee_id, employee_name, manager_id (nullable, NULL for the CEO), department, title, and salary.

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  manager_id INTEGER,
  department TEXT,
  title TEXT,
  salary NUMERIC(12,2)
);

SQL Solution

WITH RECURSIVE org_chain AS (
  SELECT
    employee_id AS manager_id,
    employee_id AS report_id,
    0 AS depth
  FROM employees
  UNION ALL
  SELECT
    oc.manager_id,
    e.employee_id AS report_id,
    oc.depth + 1
  FROM org_chain oc
  JOIN employees e ON e.manager_id = oc.report_id
),
rollup AS (
  SELECT
    oc.manager_id,
    COUNT(*) FILTER (WHERE depth = 1) AS direct_reports,
    COUNT(*) FILTER (WHERE depth >= 1) AS total_reports,
    MAX(depth) AS max_depth_below,
    SUM(e.salary) FILTER (WHERE depth >= 1) AS total_reports_salary_cost
  FROM org_chain oc
  JOIN employees e ON e.employee_id = oc.report_id
  GROUP BY oc.manager_id
)
SELECT
  m.employee_id,
  m.employee_name,
  m.title,
  m.department,
  COALESCE(r.direct_reports, 0) AS direct_reports,
  COALESCE(r.total_reports, 0) AS total_org_size,
  COALESCE(r.max_depth_below, 0) AS org_depth_below,
  COALESCE(r.total_reports_salary_cost, 0) AS total_reports_salary_cost,
  CASE
    WHEN COALESCE(r.total_reports, 0) = 0 THEN 'Individual Contributor'
    WHEN r.direct_reports >= 4 AND r.max_depth_below <= 1 THEN 'Wide & Flat'
    WHEN r.direct_reports <= 2 AND r.max_depth_below >= 2 THEN 'Narrow & Deep'
    ELSE 'Balanced'
  END AS span_of_control_profile
FROM employees m
LEFT JOIN rollup r ON r.manager_id = m.employee_id
ORDER BY total_org_size DESC NULLS LAST, m.employee_id;

Explanation