managers-with-at-least-5-direct-reports

< Previous 　　　　　　　　　　　　　　　　 Next >

570. Managers with at Least 5 Direct Reports (Medium)

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.

Related Topics

[Database]

Hints

Hint 1

Try to get all the mangerIDs that have count bigger than 5

Hint 2

Use the last hint's result as a table and do join with origin table at id equals to managerId

Hint 3

This is a very good example to show the performance of SQL code. Try to work out other solutions and you may be surprised by running time difference.

Hint 4

If your solution uses 'IN' function and runs more than 5 seconds, try to optimize it by using 'JOIN' instead.