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 ;

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

I am giving interviews and your videos has been really helpful. Keep up the work!!

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 💪

Very nice explanation.

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

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

Thankyou for making it easy to understand.

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

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.

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

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.

fantastic stuff, thanks a lot!

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

Thank you so much! You are awesome

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

best

Can you kindly solve more hard questions?

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

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