LeetCode 570: Managers with at Least 5 Direct Reports [SQL]

Solution and walkthrough of leetcode database problem 570: Managers with at Least 5 Direct Reports. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.
Link to the problem: leetcode.com/problems/manager...
Playlists:
LeetCode database SQL problems: • LeetCode Database Prob...
Easy SQL problems: • LeetCode Easy Database...
Medium SQL problems: • LeetCode Medium Databa...
Hard SQL problems: • LeetCode Hard Database...

[@IAMSAHAY]

Man I respect you for your skills

[@raghuramsharma2603]

Great explanation thanks mate...here is how i wrote the first time. select distinct e2.name from Employee e1 inner join (select name , id from Employee group by id) e2 on e1.managerId = e2.id group by e2.name having count(e1.id) >= 5

[@UmaAndLak]

Here is another answer, with self join that works. select distinct m.name from employee e left outer join employee m on e.managerid = m.id group by e.managerID having count(e.ID) >= 5

[@imdeepu7855]

select name from employee where employee.manager_id IN (select manager_id from employee e group by manager_id having count(manager_id)>5)

[@amydong1361]

Really Appreciated your videos. It helps me a lot when I prepare for my technical interviews. Just wondering are you planing to have some leetcode database python ones?

[@frederikmuller]

Database problems are SQL only on LeetCode. Check out StrataScratch for Python problems.

[@amydong1361]

@@frederikmuller Thank you for the quick reply! Will keep watching the videos on your channel. Hope you can provide more Python solutions. Your videos are so logical and easy to understand.

[@frederikmuller]

@@amydong1361 Thank you so much!

[@bhaskarpathak8094]

Try this also select a.name FROM employee a LEFT join employee b ON a.id = b.managerId HAVING COUNT(a.name) >= 5

[@containthis4450]

this question is under joins, should we use a join statement instead of a sub query?

[@frederikmuller]

you could also use a self join with the following condition: a.id = b.manager_id then you would also have to filter on managers that have at least 5 matching employees (5 rows)

[@vinayak186f3]

@4:19 I think the solution will work even if we remove the distinct clause.

[@frederikmuller]

you’re right. with DISTINCT this even works when there are duplicate employee entrees

[@echoge7683]

Really appreciate your video. But why you start jumping some SQL questions?

[@frederikmuller]

wanted to get some more medium and hard questions

[@shalvishrivastava5301]

Hey am I wrong. Shouldn’t it be having count managerid >= 5

[@frederikmuller]

That would be counting how many managers someone has. Since we‘re trying to find employees per manager, we‘re using count distinct id (employeeid).

[@shalvishrivastava5301]

@@frederikmuller I feel so stupid. 🤣 Thank you and hats off to all your work, it helps me and a lot of us immensely. Please keep up the goodwork. 👍