Problem Description –
Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
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:
Difficulty Level – Medium
SELECT m.Name FROM Employee e JOIN Employee m ON e.ManagerId = m.Id GROUP BY m.Name HAVING COUNT(*) >= 5
To solve this problem, we have to first self-join the Employee table. The first table will be for employees and the second table will be for the manager. As we want to join the manager’s table to the employee table we need to find a key in the employee table that is common with the manager’s table, that is why we have used
ON e.ManagerId = m.Id If we run the following query
SELECT e.*, m.* FROM Employee e JOIN Employee m ON e.ManagerId = m.Id
The output will be –
We can see that for all the 5 employees, their manager is john. So, in the outer query, we are grouping by the manager name to count how many entries we have.
SELECT m.name, COUNT(*) report_count FROM Employee e JOIN Employee m ON e.ManagerId = m.Id GROUP by m.name
In the problem, we are asked to show only the name of the manager who has at least 5 direct reports, so we are using the Having clause to filter the result with
HAVING COUNT(*) >= 5 and leaving only the name in the select clause.