185. Department Top Three Salaries (WITH/AS, DENSE_RANK, PARTITION BY, ORDER BY)

[문제]

 

문제 링크

https://leetcode.com/problems/department-top-three-salaries/description

 

 

Table: Employee

id is the primary key column for this table.
departmentId is a foreign key 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

id is the primary key column for this table.
Each row of this table indicates the ID of a department and its name.
 
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.

Example 1:

 

Explanation: 

In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

 

 

[문제 설명 및 풀이 방법]

department별로 salary가 가장 큰 값부터 순위(dense rank)를 정해서

3위까지 department, employee, salary 출력하기

 

 

---

 

[나의 풀이]

-- 1. 부서별로 순위 정하기
WITH salarys AS
(SELECT departmentId
     , name
     , salary
     , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM employee)

-- 2. 순위가 1, 2, 3위인 것을 출력하고 부서명을 출력하기
SELECT d.name AS Department 
     , s.name AS Employee 
     , s.salary AS salary
FROM salarys s
     INNER JOIN department d ON d.id = s.departmentId
WHERE s.salary_rank <= 3

---

[설명]

1. 부서별로 순위 정하기

WITH salarys AS
(SELECT departmentId
     , name
     , salary
     , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM employee)


SELECT *
FROM salarys

출력 결과

DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC)

departmentid별로 salary가 내림차순으로 dense_rank를 정하면 다음과 같다.

> departmentid 1이고 salary가 9000으로 가장 큰 Max가 1위

> departmentid 1이고 salary가 8500으로 두 번째로 큰 Joe, Randy가 공동 2위

> departmentid 1이고 salary가 7000으로 세 번째로 큰 Will이 3위

> departmentid 1이고 salary가 6900으로 네 번째로 큰 Janet이 4위

 

 

참고) ROW_NUMBER(), DENSE_RANK(), RANK()

2023.06.19 - [MYSQL] - 순위 정하기 ( ROW_NUMBER(), DENSE_RANK(), RANK() )

 

 

 

2. 순위가 1, 2, 3위인 것을 출력하고 부서명을 출력하기

WITH salarys AS
(SELECT departmentId
     , name
     , salary
     , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM employee)

SELECT d.name AS Department 
     , s.name AS Employee 
     , s.salary AS salary
FROM salarys s
     INNER JOIN department d ON d.id = s.departmentId
WHERE s.salary_rank <= 3

출력 결과

 

---

 

[다른 풀이]

SELECT d.name AS Department 
     , s.name AS Employee 
     , s.salary AS salary
FROM (SELECT departmentId
     , name
     , salary
     , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM employee) s
     INNER JOIN department d ON d.id = s.departmentId
WHERE s.salary_rank <= 3

> [나의 풀이]에서 WITH를 이용하여 만든 테이블 대신

   FROM절에 직접 넣어 출력한다.