Amazon SQL interview Questions – Leetcode 570 – Managers with at Least 5 Direct Reports.

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.