Problem –
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 an SQL query to find the employees who are high earners in each of the departments.
Return the result table in any order.
The query result format is in the following example:
Difficulty Level – Hard
Solution –
SELECT DISTINCT
Department,
EMployee,
Salary
FROM (
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 rnk
FROM Employee as e
JOIN Department as d
ON e.DepartmentId = d.Id
) t1
WHERE rnk <= 3
To solve this problem, we are using the DENSE_RANK() function and Partitioning the data by Department, so that we have a separate ranking of salary for each individual department as we are asked to do so. And we are ordering the salary in Desc order so that the highest salary gets the ranking of 1 and so on. And later in the outer query, we are filtering the data where the salary rank is less than or equal to 3 to get the top three salaries for that department. We also used DISTINCT to select only one salary in case of a tie.
This is the third version of common SQL interview questions related to salary. For others read these –
Version 1 – https://lifewithdata.com/2021/08/01/amazon-sql-interview-questions-leetcode-177-nth-highest-salary/
Version 2 – https://lifewithdata.com/2021/08/02/sql-interview-questions-leetcode-176-second-highest-salary/