Managers With At Least 5 Direct Reports - Leetcode Solution

Problem Statement:

You are given a table, Manager, with the following information:

| Column Name | Type | |-------------|---------| | Id | Integer | | Name | String |

Each row of this table contains the ID and the name of one manager.

You are given another table, Employee, with the following information:

| Column Name | Type | |-------------|---------| | Id | Integer | | Name | String | | Salary | Integer | | ManagerId | Integer |

Each row of this table contains the ID and the name of one employee, their salary, and the ID of the manager they report to.

The Employee table contains a column, ManagerId, which is a foreign key referring to the Id column of the Manager table.

Write an SQL query to find the managers with at least 5 direct reports. Return the managers' id and name.

Solution:

To solve this problem we need to count the number of employees that report directly to each manager and then filter out the managers who have less than 5 direct reports. To count the number of direct reports, we join the Manager table with the Employee table on the ManagerId column and group by the manager's id.

Here's the SQL query to solve this problem:

SELECT Manager.Id, Manager.Name FROM Manager JOIN Employee ON Manager.Id = Employee.ManagerId GROUP BY Manager.Id, Manager.Name HAVING COUNT(Employee.Id) >= 5;

Explanation:

The SELECT statement selects the Id and Name columns from the Manager table.

The JOIN statement joins the Manager table with the Employee table on the ManagerId column.

The GROUP BY statement groups the results by the manager's id and name.

The HAVING statement filters out the managers who have less than 5 direct reports by counting the number of employees with the COUNT function and comparing the result to 5.

1