570 Managers With at Least 5 Direct Reports

Description

Table: Employee

• id is the primary key (column with unique values) for this table.
• Each row of this table indicates the name of an employee, their department, and the id of their manager. If managerId is null, then the employee does not have a manager. No employee will be the manager of themself.

Problem Statement

Write a solution to find managers with at least five direct reports.

Return the result table in any order. The result format is in the following example.

Example 1:

Input:

• Employee table:

Output:

Solution

1. Find the number of direct reports for each employee.

This can be done using aggregation with COUNT() function.

1SELECT managerId, COUNT(id) 
2    FROM Employee 
3    GROUP BY managerId;

2. Find the manager’s name who has 5 or more direct reports.

This can be done by joining the above query with Employee table and checking if the count is greater than or equal to 5. The join condition can be managerId = Employee.id.

1WITH cte AS (
2SELECT managerId, COUNT(1) AS count
3    FROM Employee 
4    GROUP BY managerId
5)
6SELECT name FROM Employee
7JOIN cte
8ON Employee.id = cte.managerId
9WHERE cte.count >=5;