Find managers with at least 5 direct reports

Question

Accepted Answer

150
   Find managers with at least 5 direct reports
  Level: Coding Round — A LeetCode classic; tests self-referencing aggregation and the GROUP BY + HAVING count pattern.

  
     The Puzzle: Given an employees table where each row has a manager_id, find the names of all managers who have at least 5 direct reports. (For a smaller demo we will use a threshold of 3.)
  

   Sample Data — employees
  
    
      
        idnamemanager_id
      
      
        1AjayNULL
        2Rumman1
        3Krushna1
        4Swetha1
        5Ritesh2
        6Manjula2
      
    
  

   Expected Output (threshold = 3)
  
    
      name
      
        Ajay
      
    
  
  Ajay (id 1) manages 3 people (Rumman, Krushna, Swetha) &rarr; qualifies. Rumman (id 2) manages only 2 &rarr; excluded.

  
     The core idea: Group the table by manager_id and count the rows in each group &mdash; that count is the number of direct reports. Use HAVING COUNT(*) >= 5 to keep only managers meeting the threshold, then join back to get the manager's name (since the group key is the manager's id).
  

   Solution 1 — GROUP BY + HAVING, then Join for the Name
SELECT e.name
FROM employees e
JOIN (
    SELECT manager_id
    FROM employees
    WHERE manager_id IS NOT NULL       -- ignore the top-level CEO
    GROUP BY manager_id
    HAVING COUNT(*) >= 5               -- at least 5 direct reports
) m ON e.id = m.manager_id;

  
     Why join back to employees: The GROUP BY gives you the manager's id, not their name (the name lives in a different row). You must join the aggregated result back to employees on e.id = manager_id to translate the id into a name. Forgetting this join is the most common mistake.
  

   Solution 2 — Correlated Subquery
SELECT e.name
FROM employees e
WHERE (
    SELECT COUNT(*)
    FROM employees r
    WHERE r.manager_id = e.id       -- count employees reporting to e
) >= 5;

   Solution 3 — Self-Join with GROUP BY
SELECT m.name
FROM employees m
JOIN employees r ON r.manager_id = m.id    -- r = the reports, m = the manager
GROUP BY m.id, m.name
HAVING COUNT(r.id) >= 5;

  
     Solution 3 is often the cleanest: The self-join directly pairs each manager (m) with their reports (r), so grouping by the manager and counting reports reads naturally. It also gives you the name in one query without a separate join-back step.
  

  
     Interviewer follow-up: "Now count ALL reports &mdash; direct AND indirect (the whole subtree below each manager)." &rarr; That is no longer a simple GROUP BY, because it spans multiple levels. Use a recursive CTE to walk down the hierarchy from each manager and count every descendant. This is where the earlier recursive-CTE org-chart technique (Q135) comes back &mdash; direct reports use GROUP BY, full subtrees need recursion.

Find managers with at least 5 direct reports

Answer with Explanation

Find managers with at least 5 direct reports

Sample Data — `employees`

Expected Output (threshold = 3)

Solution 1 — GROUP BY + HAVING, then Join for the Name

Solution 2 — Correlated Subquery

Solution 3 — Self-Join with GROUP BY