The Number Of Employees Which Report To Each Employee - Leetcode Solution

Problem:

You are given a table, Employee, representing the employees of a company:

Employee

-id Integer -name String -department_id Integer -manager_id Integer Write an SQL query to find the number of employees for each manager.

For example, given the following Employee table:

+----+-------+---------------+------------+ | id | name | department_id | manager_id | +----+-------+---------------+------------+ | 1 | Joe | 1 | 3 | | 2 | Henry | 2 | 4 | | 3 | Sam | 1 | NULL | | 4 | Max | 2 | NULL | +----+-------+---------------+------------+ +-------+------------------+ | name | Number of Employees | +-------+------------------+ | Joe | 1 | | Henry | 1 | | Sam | 2 | +-------+------------------+

Solution:

To solve this problem, we can use a subquery to find the number of employees reporting to each manager. In the subquery, we can group the employees by their manager_id and use the count() function to get their total count.

Then, we can join this subquery with the Employee table to get the name of each manager and the number of employees reporting to them.

Here's the SQL query for the solution:

SELECT e.name, COUNT() AS 'Number of Employees' FROM Employee e LEFT JOIN ( SELECT IFNULL(manager_id, id) AS manager_id, COUNT() AS employee_count FROM Employee GROUP BY IFNULL(manager_id, id) ) AS reports ON e.id = reports.manager_id GROUP BY e.id ORDER BY e.id;

Explanation:

In the subquery, we use the IFNULL() function to get the manager_id of each employee. If an employee does not have a manager, we use their own id as the manager_id. Then, we group the employees by their manager_id and use the count() function to get the total count of employees reporting to each manager.

In the main query, we use a LEFT JOIN to join the Employee table with the subquery. This ensures that we include all managers in the result, even if they do not have any employees reporting to them.

We select the name of each manager and the count of employees reporting to them using the COUNT() function and group the result by the manager_id. Finally, we order the result by the manager's id.

Thus, we get the desired output with the number of employees which report to each employee.

1