Easy solution with explanation - 90% faster
Aniruddha Shinde
74

Explanation -

This was a piece of cake for a Leetcode Hard.

Tasks - So we want to do 2 things that would need some sort of transformation/aggregation on existing data.

  1. We want to map the department id's to department names (1 -> IT, 2 -> Sales, etc). As we want to display only department names in the final output.
  2. Now we want add some aggregation logic that'll sort salary's and group them by department so we can grab the top whatever number we want.

Great - Now that we have our tasks clear. Lets go for the logic.

  • We do a simple inner join for Employee and Department tables on DeparmentId to get the output as required specified in task #1.
  • Next this is the trickier part I am using the rank window function to partition over department and order by salary desc. But specifically so I am using dense_rank() which is the slightly unpopular rank function variant as apposed to rank() and row_number(). The reason for that? Simple we don't want duplicate salary's to be included at the expense of next lower salary's.
+--------+--------+-----------+--------------+
| Salary | rank() | row_num() | dense_rank() |
+--------+--------+-----------+--------------+
|90000   | 1      | 1         | 1            |
+--------+--------+-----------+--------------+
|90000   | 1      | 2         | 1            |
+--------+--------+-----------+--------------+
|90000   | 1      | 3         | 1            |
+--------+--------+-----------+--------------+
|85000   | 4      | 4         | 2            |
+--------+--------+-----------+--------------+
|70000   | 5      | 5         | 3            |
+--------+--------+-----------+--------------+

As can be seen above dense_rank preserves the ordering of ranks even if there are duplicates, rank increments the rank for every duplicate. Row num is the least likely able to fit option here as it assigns new rank to every salary even if they are duplicates.

So we use dense_rank and filter all the salary's with rank under or equal to 3.

The full query can be seen as below.

# Write your MySQL query statement below
# Tables given - Employee (id is pk) | Department (id is pk)

# Step 1 - Let's get the department ids out of the way and assign employees with department names instead.
with dept_sals as
(
    select 
        d.name as Department,
        e.name as Employee,
        e.salary as Salary
    from 
        employee e
        inner join department d
        on e.departmentid = d.id
),

# Step 2 - Now we'll add a dense rank window on department orderd by salary
ranked_sal as 
(
    select
        *,
        dense_rank() over (partition by Department order by Salary desc) rnk
    from
        dept_sals
)

# Step 3 - Since we want to get the top 3 we'll filter top 3 ranks from the previous subquery.
select
    Department,
    Employee,
    Salary
from
    ranked_sal
where 
    rnk <= 3
Comments (0)