Organization Hierarchy With Subordinate Counts (Recursive CTE)

WITH RECURSIVE org AS (SELECT employee_id, first_name, last_name, manager_id, 1 AS org_level, first_name || ' ' || last_name AS management_path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, o.org_level + 1, o.management_path || ' -> ' || e.first_name || ' ' || e.last_name FROM employees e INNER JOIN org o ON e.manager_id = o.employee_id), sub AS (SELECT a.employee_id, COUNT(b.employee_id) - 1 AS total_subordinates FROM org a LEFT JOIN org b ON b.management_path LIKE a.management_path || '%' GROUP BY a.employee_id) SELECT o.employee_id, o.first_name, o.last_name, o.org_level, o.management_path, s.total_subordinates FROM org o INNER JOIN sub s ON s.employee_id = o.employee_id ORDER BY o.org_level, o.employee_id;

Why this works

Recursive CTEs walk arbitrary-depth hierarchies. The path-prefix LIKE trick is a neat way to count an entire subtree without a second recursion. Note: on MySQL/SQL Server the `||` concatenation must become CONCAT/+.