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

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:

Difficulty Level – Medium
Solution –
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.