인프런 강의 <데이터 분석을 위한 고급 SQL>을 듣고, 중요한 점을 정리한 글입니다.
문제
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.
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.
Example 1:
Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+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
풀이
- 나의 틀린 풀이과정
SELECT department.name AS Department
, employee.name AS Employee
, employee.Salary
FROM employee
INNER JOIN department ON employee.departmentid = department. id: 선생님께서 RANK(), DENSE RANK() 쓰면된다고 힌트를 주셨는데, 어떻게 구현해야할 지 상상이 안 간다.
- 선생님 풀이
SELECT t.department
, t.employee
, t.salary
FROM (
SELECT department.name AS department
, employee.name AS employee
, employee.salary
, DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr
FROM employee
INNER JOIN department ON employee.departmentid = department. id
) t
WHERE t.dr <= 3배운점
- RANK()에서는 같은 숫자가 두개인 경우, 등수를 각각 센다. DENSE RANK()는 같은 숫자가 두개인 경우, 한 뭉치로 등수를 센다. 이 문제에서는 DENSE RANK()를 활용했다.
- 이 사람들이 3순위권 안에 들어있는지 계산하기 위해 DENSE RANK() 사용
