Department Top Three Salaries - LeetCode

Department Top Three Salaries 문제

출처 : LeetCode Department Top Three Salaries

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

Q.

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.

---

질문

각 부서마다 급여가 높은 '상위 3개 급여'에 해당하는 모든 직원을 찾는 문제!

예시

내 답안 📕

WITH rank_table AS (
    SELECT *
         , DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS 'rnk'
    FROM Employee
)

SELECT d.name AS Department
     , r.name AS Employee
     , r.salary AS Salary
FROM rank_table AS r
  INNER JOIN Department AS d ON r.departmentId = d.id
WHERE r.rnk <= 3

부서별 상위 3개의 급여를 뽑아내야하는데, Output 부분에 Will(70000)까지 포함돼 있으므로 DENSE_RANK()를 써야한다고 판단