Using dense_rank() to get top salaries by department.
From leetcode, the problem description is:
Table: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference column) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
Table: Department
+-------------+---------+
| Column Name | Type |
A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
Write a solution to find the employees who are high earners in each of the departments.
Return the result table in any order.
The result format is in the following example.
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of a department and its name.
Solution:
with cte as (
select d.name as Department
,e.name as Employee
,e.salary as Salary
,dense_rank() over (partition by d.name order by salary desc) as row_id_desc
from employee e
left join department d
on e.departmentid = d.id
)
select Department
,Employee
,Salary
from cte
where row_id_desc <=3
;
Why using dense_rank() instead of rank() or row_number()?
row_number will assign a unique ranking number for each row, but the requirements of the problem state that we must get the 3 top earners of unique salaries, so, in this case, row_number is not suitable.
rank(), on the other side, will allow us to assign the same ranking number for equal salaries, but will leave gaps between the rankings.
So, the viable option is dense_rank(), which will assign the same ranking number for equal salaries with no gaps between rankings.
This solution beats 100% in Leetcode.
https://lnkd.in/djPvaweQ
