sql notes: leetcode#570 mnanagers with at least 5 direct reports

Problem

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

1
2
3
4
5
6
7
8
9
10

+------+----------+-----------+----------+
|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       |
+------+----------+-----------+----------+

Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:

1
2
3
4
5

+-------+
| Name  |
+-------+
| John  |
+-------+

Note:
No one would report to himself.

Analysis

For this problem, we can group by ManagerId and find id’s of managers who have at least 5 direct reports:

1

SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(*) > 4;

Then output corresponding names:

1
2

SELECT Name FROM Employee WHERE Id IN 
(SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(*) > 4);

Also, we can join 2 Employee tables to link employees and their managers, then group by managers and filter with number of employees reporting to them directly:

1
2
3

SELECT m.Name FROM Employee AS e 
JOIN Employee AS m ON e.ManagerId = m.Id 
GROUP BY m.Name HAVING COUNT(e.Name) >= 5;

Solution

Solution 1

1
2

SELECT Name FROM Employee WHERE Id IN 
(SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(*) > 4);

Solution 2

1
2
3

SELECT m.Name FROM Employee AS e 
JOIN Employee AS m ON e.ManagerId = m.Id 
GROUP BY m.Name HAVING COUNT(e.Name) >= 5;

Link

570. Managers with at Least 5 Direct Reports
(中文版) SQL 笔记: Leetcode#570 Mnanagers with at Least 5 Direct Reports