Solution and walkthrough of leetcode database problem 185: Department Top Three Salaries. 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/p...
Пікірлер: 27
@frederikmuller3 жыл бұрын
alternative solution: select d.Name as 'Department', e1.Name as 'Employee', e1.Salary from Employee e1 join Department d on e1.DepartmentId = d.Id where 3 > ( select count(distinct e2.Salary) from Employee e2 where e2.Salary > e1.Salary and e1.DepartmentId = e2.DepartmentId ) order by Department,Salary DESC ;
@aryajoshi6163 жыл бұрын
Can we use 'offset 4' for filtering out the top 3 records?
@stellaueda45653 жыл бұрын
Thank you so much for your time. By far the best channel for SQL leetcode questions.
@frederikmuller3 жыл бұрын
so glad to hear that you like it
@pranilbhavsar61363 жыл бұрын
I am giving interviews and your videos has been really helpful. Keep up the work!!
@mohamed_ellithy Жыл бұрын
Thank you so much you explained it very clearly 😍, you helped me a lot when you said "Window Functions" I didn't know what it is. Keep Going 💪
@mhmahmud6647 ай бұрын
Very nice explanation.
@vaibhavpatharkar6794 Жыл бұрын
This was a tough problem, but u made it easy. 😅
@yuxuan23933 жыл бұрын
Thank you so much!!it's really helpful!!
@microvlog06 Жыл бұрын
Thankyou for making it easy to understand.
@avahome52852 жыл бұрын
NOTES: Top N per Y , window function. Rank(). DENSE_RANK() (1,1,2)allows ties compare to RANK().(1,1,3)
@fantasytalker3 жыл бұрын
I enjoyed watching your teaching videos so much. It has greatly helped me formulate a thinking pattern when solving the hard problems. Much appreciated. Would you maybe make a video on Leetcode-1294. Weather patterns in different countries in the future please? Thanks so much for all the efforts you made in this.
@frederikmuller2 жыл бұрын
Thank you so much for your comment. I'm currently making a lot of videos on StrataScratch problems since I set up a sponsorship with that platform. I added LeetCode 1294 to my list of requested problems nevertheless and will start with these if I get back to making LeetCode videos.
@fantasytalker2 жыл бұрын
@@frederikmuller Thank you for your response. Got it. No pressure:) I will start watching the StrataScratch videos shortly. Thanks a bunch. Take care.
@anshulkatara5682 жыл бұрын
with combine_data as (SELECT t1.name , t1.departmentId , t1.Salary , dense_rank() over (partition by t1.departmentId order by t1.salary desc) ranking FROM employee t1) select t2.name as "Department", t1.name as "Employee", t1.Salary from combine_data t1, department t2 where ranking
@akankshamishra1139 Жыл бұрын
Hi Frederik, Thanks for creating amazing stuff. You are helping millions of people learning SQL. I have a request to you- could you please solve leetcode 1194 problem for me. I am getting 2 rows instead of expected result i.e. 3 rows.
@Michael-jg3pb Жыл бұрын
fantastic stuff, thanks a lot!
@ishitvasingh99022 жыл бұрын
with cte as ( select e.name as Employee,e.salary as Salary,e.departmentid,d.name as Department, dense_rank() over (partition by e.departmentid order by e.salary desc) as rank_sal from employee as e join department as d on e.departmentid=d.id ) select Department,Employee,Salary from cte where rank_sal
@tejasviniriyer3 жыл бұрын
Thank you so much! You are awesome
@UmaAndLak2 жыл бұрын
Hi, I was stuck with this interview question yesterday! I still could not solve the problem. Could you please suggest "how you would go about solving it"? I also submitted in the leet code forum. Not sure if i am going to get an answer there! See below. Write a "SELECT" statment with 3 columns as output: person_id, login_time and session_id. Session_id is the column that you are going to be calculating: For every person_id, if the login_time is within the 30 minutes from the previous session, it will be grouped under one session_id. You could have multiple rows sharing that 30 minutes and they will all be called as session_id 1. If the login_time is outside of the 30 minutes, assign the session_id 2 and so on. The session_id calculation for each person_id is calculated seperately. In the insert statment below the session_id you would calculating is in the commented_out. How do you get those session_id as outputs? I can see the solution will have the windows function lead(), time_diff() and dense_rank(). But having a hard time putting the solution together. CREATE TABLE session_login (person_id integer, login_time time); INSERT INTO session_login VALUES (1,'00:08:40'); -- 1 INSERT INTO session_login VALUES (1,'00:08:30'); -- 1 INSERT INTO session_login VALUES(1,'00:08:55'); -- 1 INSERT INTO session_login VALUES(2,'00:09:00'); -- 1 INSERT INTO session_login VALUES(2,'00:09:25'); -- 1 INSERT INTO session_login VALUES(1,'00:10:03'); -- 2 INSERT INTO session_login VALUES(2,'00:10:30'); -- 2
@mohit48122 жыл бұрын
best
@Raghav_19953 жыл бұрын
Can you kindly solve more hard questions?
@frederikmuller3 жыл бұрын
I'll do some hard ones for a change, thanks for your feedback! I just felt like you'll mostly get easy and medium in interviews and these were also the most popular playlists here on KZfaq.
@Raghav_19953 жыл бұрын
@@frederikmuller Thanks, that sounds cool too! I just wanted to concentrate on hard more, but your logic seems good too. Would like to see more SQL Leetcode questions. You have literally made me well versed in SQL. Thanks, a lot!
@ignaciogonzalez26192 жыл бұрын
Amazing videos really ... It is helping me a lot just a quick question does the following queries would work: WITH Max_Salary_Department AS (SELECT Salary, DepartmentId, Name AS Employee FROM DepartmentId Employee GROUP BY DepartmentId ORDER BY Salary DESC LIMIT 3) SELECT Max_Salary_Department.Salary, Max_Salary_Department.Employee, Department.Name AS Department FROM Max_Salary_Department
@wizzard2112 жыл бұрын
WITH temp AS ( SELECT d.name as Department, e.name as Employee, e.salary as Salary, DENSE_RANK() OVER (PARTITION BY d.name ORDER BY e.salary DESC) AS DenseRank FROM Employee e JOIN Department d ON e.departmentId = d.id ) SELECT Department, Employee, Salary FROM temp WHERE DenseRank