LeetCode 570. Managers with at Least 5 Direct Report SQL Solution
Problem
LeetCode SQL Problem
- Managers with at Least 5 Direct Report
Employee table
| Id | Name | Department | ManagerId |
|---|---|---|---|
| 101 | John | A | |
| 102 | Dan | A | 101 |
| 103 | James | A | 101 |
| 104 | Amy | A | 101 |
| 105 | Anne | A | 101 |
| 106 | Ron | B | 101 |
Solution
- MySQL
- TSQL
Managers with at Least 5 Direct Report
WITH MgrWithAtLeastFiveDirectReport
AS (
SELECT M.ManagerId
FROM Employee AS M
GROUP BY M.ManagerId
HAVING count(M.Id) >= 5
)
SELECT E.Name
FROM MgrWithAtLeastFiveDirectReport AS M
INNER JOIN Employee AS E ON M.ManagerId = E.Id
Managers with at Least 5 Direct Report
WITH MgrWithAtLeastFiveDirectReport
AS (
SELECT M.ManagerId
FROM Employee AS M
GROUP BY M.ManagerId
HAVING count(M.Id) >= 5
)
SELECT E.Name
FROM MgrWithAtLeastFiveDirectReport AS M
INNER JOIN Employee AS E ON M.ManagerId = E.Id
Query Output
| Name |
|---|
| John |