LeetCode 185: Department Top Three Salaries [SQL]

Поделиться
HTML-код
  • Опубликовано: 11 дек 2024

Комментарии • 28

  • @frederikmuller
    @frederikmuller  4 года назад +13

    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
      @aryajoshi616 3 года назад

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

  • @stellaueda4565
    @stellaueda4565 4 года назад +9

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

  • @pranilbhavsar6136
    @pranilbhavsar6136 3 года назад +1

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

  • @avahome5285
    @avahome5285 3 года назад +1

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

  • @mohamed_ellithy
    @mohamed_ellithy 2 года назад

    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
    @vaibhavpatharkar6794 Год назад

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

  • @yuxuan2393
    @yuxuan2393 4 года назад +2

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

  • @microvlog06
    @microvlog06 2 года назад

    Thankyou for making it easy to understand.

  • @mhmahmud664
    @mhmahmud664 10 месяцев назад

    Very nice explanation.

  • @Michael-jg3pb
    @Michael-jg3pb Год назад

    fantastic stuff, thanks a lot!

  • @anshulkatara568
    @anshulkatara568 2 года назад

    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

  • @UmaAndLak
    @UmaAndLak 2 года назад

    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

  • @fantasytalker
    @fantasytalker 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.

    • @frederikmuller
      @frederikmuller  3 года назад +1

      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
      @fantasytalker 3 года назад +1

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

  • @tejasviniriyer
    @tejasviniriyer 3 года назад

    Thank you so much! You are awesome

  • @ishitvasingh9902
    @ishitvasingh9902 2 года назад

    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

  • @akankshamishra1139
    @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.

  • @sachin-b8c4m
    @sachin-b8c4m Месяц назад

    thanks

  • @Raghav_1995
    @Raghav_1995 3 года назад

    Can you kindly solve more hard questions?

    • @frederikmuller
      @frederikmuller  3 года назад

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

    • @Raghav_1995
      @Raghav_1995 3 года назад +1

      @@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
    @mohit4812 2 года назад

    best

  • @wizzard211
    @wizzard211 3 года назад

    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
    @ignaciogonzalez2619 2 года назад

    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