Find a managers with at least 3 direct reports

SQL query to find manager who have at least 3 direct reports.

Here, we have a sample table named Employee.

Create table Employee
(Id INT, Name VARCHAR(50), Department VARCHAR(50), ManagerId INT)

INSERT INTO Employee values (501, 'Rajesh Singh', 'CS', null)
INSERT INTO Employee values (502, 'Amrish Kumar', 'CS', 501)
INSERT INTO Employee values (503, 'Atul', 'CS', 501)
INSERT INTO Employee values (504, 'Mukesh', 'Eng.', 501)
INSERT INTO Employee values (505, 'Jhone', 'Eng.', null)
INSERT INTO Employee values(506, 'Ram Prasad', 'CS', 505)
INSERT INTO Employee values (507, 'Manoj', 'CS', 505)
INSERT INTO Employee values (508, 'Rakesh Rawat', 'Eng', 505)
INSERT INTO Employee values (509, 'Manish', 'CS', null)

You can see the data in a table.

Solution:

The following T-SQL query returns managers having at least three direct reports, as well as their direct reports counts.

With cte as
(
SELECT e.Id AS EmpId, e.name AS Emp, m.id AS MgrId, m.name as Manager
FROM Employee e LEFT OUTER JOIN Employee m
ON e.managerId = m.id 
)
SELECT Manager AS Name, Count(MgrId) AS Cnt FROM cte 
WHERE MgrId IS NOT NULL GROUP BY Manager
HAVING COUNT(MgrId)>=3;

Let’s look at the output of the above query. It returns two managers with at least three direct reports.

 

Also Read..

SQL Interview Questions and Answers