LeetCode 1270: All People Report to the Given Manager [SQL]

[@CC-ju6hw]

The slide at 4:50 helps tremendously on the thought process where I got stuck at. Thank you!

[@mindyng1124]

the diagram helped me understand multiple self-joins a lot bc with them can clearly see what rows are being joined and resultant table. thank you!

[@frederikmuller]

I'm glad it helped you understand these joins, I figured it would be worth visualizing them for this problem.

[@legion_29]

Awesome, thank you very much. I did write script as well which had a run time of 115 ms, but your script seems more optimal and faster. thank you.

[@pranavsharma4340]

Hey Frederik, I am really looking forward to your videos!

[@parveshkiran7217]

hey bro...ur work is good..and ur solving technique is so simple

[@frederikmuller]

thank you!

[@radhakrishnarajupenmetcha6744]

Hey Fredrick. Thank you for making SQL videos. Can you please also explain thought process of how to solve the same problem with a recursive approach so that it would work for any number of levels in hierarchy? Thanks!

[@vijayarana2087]

well explained !!!thanks

[@rahulpoptani90]

Solution in Oracle. This would return hierarchy for all levels. Uncomment to restrict to some levels. select employee_id from employee where employee_id 1 -- and level

[@minakshi_119]

with cte as (select employee_id from Emp1 where manager_id in( select employee_id from Emp1 where manager_id in( select employee_id from Emp1 where manager_id=1))) select employee_id from cte where employee_id!=1 is this correct??

[@legion_29]

Could you also add Leetcode 1364. Number of Trusted Contacts of a Customer to your video list. Seems to be a complex problem for me. thank you.

[@frederikmuller]

should be out in the upcoming week :)

[@YASHKUMARJAIN]

Hi, i dont have leetcode premimum. So just by reading question I gave a try by this way:- select distinct a.employee_id from employess a join employees b on a.employee_id=b.employee_id join employess c on b.employee_id=c.employee_id where (a.manager_id=1 or (a.manager_id=b.employee_id and b.manager_id=1) or (a.manager_id=b.employee_id and b.manager_id=c.employee_id and c.manager_id=1) and a.employee_id !=1);

[@shivarajhalageri2513]

🙌

[@deetee8051]

solution for level more than 3 with a recursive loop - with emp as( -- parent table sel *, 1 as lvl from Employee e where e.manager_id = 1 union all -- all children tables regardless how many indirect levels there are sel *, lvl +1 as lvl from emp inner join Employee e on emp.employee_id = e.manager_id ) sel employee_id from emp where emp.lvl > 1

[@lifeisbeautiful8441]

hey bro! i think you have missed corner cases when manager is at level 1 or at level 2 Just check out with this testcase: {"headers":{"Employees":["employee_id","employee_name","manager_id"]},"rows":{"Employees":[[4,"Boss",1],[8,"Alice",6]]}}

[@frederikmuller]

it works with the boss being at level 1 or at level 2 if the entry for boss is in the input table. your example is missing the entry for employees 1 and 6, the entry for 1 being [1,"Boss",1]. the trick is that the boss links to themself which can't be done when the entry for the boss is missing. hope that's clear.

[@daniellesong3686]

Hi Frederik, thanks for the solution! Can you please help on my code which i wrote before viewing your solution? It's way more compliated than yours, but i still want to know why the error "duplicate column name 'manager_id' " returns in leetcode, thank you. Here's the code SELECT d.id AS employee_id FROM (SELECT a. employee_id AS id, a.manager_id, b.employee_id, b.manager_id, c.employee_id, c.manager_id FROM (SELECT employee_id, IF(employee_id=manager_id, NULL, manager_id) AS manager_id FROM Employees) a JOIN Employees b ON a.manager_id=b.employee_id JOIN Employees c ON b.manager_id=c.employee_id) d WHERE c.manager_id=1