How do you find the manager with the most direct reports?

Short answer: GROUP BY ManagerId, COUNT(*), order descending, TOP 1. Join back for manager name.

Sample solution

SELECT TOP (1) m.EmpId, m.Name, COUNT(*) AS DirectReports
FROM Employees e
INNER JOIN Employees m ON e.ManagerId = m.EmpId
GROUP BY m.EmpId, m.Name
ORDER BY DirectReports DESC;

Ask how to break ties — TOP 1 WITH TIES may be desired.