LeetCode 코딩 테스트 - Department Top Three Salaries(LV.Hard)

 

 

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

 

Constraints:

• There are no employees with the exact same name, salary and department.

 

# 쿼리를 작성하는 목표, 확인할 지표 : 각 부서에서 탑 3의 연봉값을 가지고 있는 직원 추출 / salary
# 쿼리 계산 방법 : 1. 부서별 연봉(distinct) 탑 3 추출 -> 2. 탑3와 같은 연봉을 가지고 있는 직원 추출 -> 3. join으로 부서명 붙이기 
# 데이터의 기간 : x
# 사용할 테이블 : employee, department
# JOIN KEY : departmentid, id
# 데이터 특징 : x
# 1
WITH top_3 AS (
    SELECT
        departmentid,
        salary
    FROM (
        SELECT
            departmentid,
            salary,
            DENSE_RANK() OVER(PARTITION BY departmentid ORDER BY salary DESC) AS rnk
        FROM employee
    ) AS a
    WHERE
        rnk IN (1,2,3)
), base AS (
    # 2
    SELECT
        DISTINCT
            e.name,
            e.salary,
            e.departmentid
    FROM employee AS e
    INNER JOIN  top_3 AS t
    ON e.salary = t.salary AND e.departmentid = t.departmentid
)
# 3
SELECT
    d.name AS department,
    b.name AS employee,
    b.salary AS salary
FROM base AS b
INNER JOIN department AS d
ON b.departmentid = d.id