Managers with at Least 5 Direct Reports in SQL

Question

Given an `employee` table (id, name, department, managerId), find the managers who have at least 5 direct reports.

Accepted Answer

Optimal Solution:
SELECT m.name
FROM employee m
JOIN (
  SELECT managerId
  FROM employee
  WHERE managerId IS NOT NULL
  GROUP BY managerId
  HAVING COUNT(id) >= 5
) r ON m.id = r.managerId;

Technical Explanation:
The inner query groups by `managerId` and filters `HAVING COUNT(id) >= 5`. The outer query joins with `employee` on `id = managerId` to retrieve the manager's name.

id	name	managerId
101	John	NULL
102	Dan	101
103	James	101
104	Amy	101
105	Anne	101
106	Ron	101