How I solved LeetCode Challenge, All People Report to the Given Manager
Hello Everyone,
Today I will be going over how I solved LeetCode Challenge, All People Report to the Given Manager, in SQL.
Objective: Write an SQL query to find employee_id of all employees that directly or indirectly report their work to the head of the company.
The indirect relation between managers will not exceed 3 managers as the company is small. Return results table in any order without duplicates.
The head of the company is the employee with employee_id 1.The employees with employee_id 2 and 77 report their work directly to the head of the company.The employee with employee_id 4 report his work indirectly to the head of the company 4 --> 2 --> 1. The employee with employee_id 7 report his work indirectly to the head of the company 7 --> 4 --> 2 --> 1.The employees with employee_id 3, 8 and 9 don't report their work to head of company directly or indirectly.
I decided to solve this problem using a with clause to keep it cleaner since I would be referring to the employees table a few time. The with clause will be selecting those who are directly reporting to the Boss. They have a manager id of 1 and to exclude the Boss we add to the where clause not equal to 1 (Boss’s employee id).
The next few steps will be handled using a union all statement. This will allow me to pull those who are indirectly reporting to the Boss. In this union all statement the first query will be pulling the information from the with clause we called dirc.
The second query in the union all is our second level of employees reporting to the Boss. This query will have a where clause that returns employees that have a manger id that matches the employees returned from the with clause (directly reporting to Boss).
The last query in the union all is the third level reporting to the Boss. It will return employees who have a manager id matching those return in the second level.
This is the final query:
with dirc as
(select employee_id
from employees
where manager_id = 1
and employee_id <> 1)select *
from
(select employee_id
from dirc
union all
select employee_id
from employees
where manager_id in (select employee_id from dirc)
union all
select employee_id
from employees
where manager_id in (select e.employee_id
from employees e
where manager_id in (select employee_id from dirc))
);
It passes!
Please let me know what you think! Hope you like today’s blog. Until next time!
