Leetcode: Managers with at Least 5 Direct Reports

Managers with at Least 5 Direct Reports



Similar Problems:


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

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

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

Note:
No one would report to himself.

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.


## Blog link: https://code.dennyzhang.com/managers-with-at-least-5-direct-reports
select Name
from Employee
where id in
   (select ManagerId
    from Employee
    group by ManagerId
    having count(1) >= 5)

## Solution with inner join
##    select Employee.Name
##    from (select ManagerId
##        from Employee
##        group by ManagerId
##        having count(1) >= 5) as t
##        inner join
##        Employee
##    on t.ManagerId = Employee.Id
linkedin
github
slack

Share It, If You Like It.

Leave a Reply

Your email address will not be published.