Leetcode SQL: 570 Managers with at Least 5 Direct Reports

SELECT e.name
FROM Employee e
JOIN (
    SELECT managerId
    FROM Employee
    WHERE managerId IS NOT NULL
    GROUP BY managerId
    HAVING COUNT(*) >= 5
) AS ManagersWithReports
ON e.id = ManagersWithReports.managerId;

Explanation:

1. Subquery – Find Managers with at Least 5 Reports:

   • We first create a subquery to count how many direct reports each manager has.
   • We filter out rows where managerId is NULL because employees without a manager should not be included.
   • The GROUP BY managerId groups the rows by managerId, so we can count how many employees report to each manager.
   • The HAVING COUNT(*) >= 5 condition ensures that we only keep managers who have 5 or more direct reports.

2. Main Query – Retrieve Manager Names:

   • In the main query, we join the Employee table with the subquery.
   • The ON e.id = ManagersWithReports.managerId ensures that we retrieve the name of the manager from the Employee table where their id matches the managerId from the subquery.

3. Result:

   • This query returns the names of the managers who have at least five direct reports.

Example Walkthrough:

For the given table:

• Employee:

  +-----+-------+------------+-----------+
  | id  | name  | department | managerId |
  +-----+-------+------------+-----------+
  | 101 | John  | A          | null      |
  | 102 | Dan   | A          | 101       |
  | 103 | James | A          | 101       |
  | 104 | Amy   | A          | 101       |
  | 105 | Anne  | A          | 101       |
  | 106 | Ron   | B          | 101       |
  +-----+-------+------------+-----------+

The output would be:

+------+
| name |
+------+
| John |
+------+

Explanation:

• John (id = 101) is the manager of Dan, James, Amy, Anne, and Ron, which totals 5 direct reports.
• Since John has 5 direct reports, his name is returned.

Key Concepts:

• Subquery:
  We use a subquery to calculate how many direct reports each manager has by counting the number of employees with the same managerId.

• HAVING Clause:
  The HAVING clause is used to filter groups after aggregation (in this case, the COUNT(*)), ensuring we only keep managers with at least 5 direct reports.

• JOIN:
  The JOIN connects the subquery results back to the Employee table to retrieve the names of the managers.

Alternative Solution:

You could also write this query without a subquery using JOIN and GROUP BY directly:

SELECT e.name
FROM Employee e
JOIN Employee e2 ON e.id = e2.managerId
GROUP BY e.id, e.name
HAVING COUNT(*) >= 5;

Similar SQL Problems:

1. LeetCode 176: Second Highest Salary
   This problem also involves using aggregation functions to filter results based on conditions, similar to using COUNT(*) with HAVING in this problem.

2. LeetCode 181: Employees Earning More Than Their Managers
   This problem involves joining tables and filtering results based on employee-manager relationships.

3. LeetCode 196: Delete Duplicate Emails
   This problem focuses on identifying and filtering specific rows, much like how we identify managers with at least five direct reports here.

Let me know if you’d like further clarification or another problem!

LeetCode Medium 570 "Managers with 5 Direct Reports" Amazon Interview SQL Question With Explanation by Everyday Data Science