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.
답
SELECT
t1.Department
,t1.Employee
,t1.Salary
FROM (
SELECT
d.name AS 'Department'
,e.name AS 'Employee'
,e.salary AS 'Salary'
,DENSE_RANK() OVER(PARTITION BY e.departmentId ORDER BY e.salary DESC) AS 'Rank'
FROM Employee e
JOIN Department d ON e.departmentId = d.id
) AS t1
WHERE t1.Rank <= 3
;