LeetCode 185: Department Top Three Salaries [SQL]

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...

[@frederikmuller]

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
;

[@aryajoshi616]

Can we use 'offset 4' for filtering out the top 3 records?

[@stellaueda4565]

Thank you so much for your time. By far the best channel for SQL leetcode questions.

[@frederikmuller]

so glad to hear that you like it

[@pranilbhavsar6136]

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 💪

[@vaibhavpatharkar6794]

This was a tough problem, but u made it easy. 😅

[@fantasytalker]

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.

[@frederikmuller]

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.

[@fantasytalker]

@@frederikmuller Thank you for your response. Got it. No pressure:) I will start watching the StrataScratch videos shortly. Thanks a bunch. Take care.

[@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.

[@avahome5285]

NOTES: Top N per Y , window function. Rank(). DENSE_RANK() (1,1,2)allows ties compare to RANK().(1,1,3)

[@microvlog06]

Thankyou for making it easy to understand.

[@UmaAndLak]

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

[@mhmahmud664]

Very nice explanation.

[@sachin-b8c4m]

thanks

[@yuxuan2393]

Thank you so much!!it's really helpful!!

[@Michael-jg3pb]

fantastic stuff, thanks a lot!

[@anshulkatara568]

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

[@ishitvasingh9902]

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

[@tejasviniriyer]

Thank you so much! You are awesome

[@Raghav_1995]

Can you kindly solve more hard questions?

[@frederikmuller]

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 TH-cam.

[@Raghav_1995]

@@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!

[@mohit4812]

best

[@wizzard211]

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

[@ignaciogonzalez2619]

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