[MySQL] 서브쿼리 해커랭크(Top Earners), 리트코드(Department Highest Salary) 예제문제 풀어보기

Department Highest Salary

문제

We define an employee's total earnings to be their monthly salary x months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

Sample Input

Sample Output

69952 1

Explanation

The table and earnings data is depicted in the following diagram:

문제 풀이

SELECT MAX(months*salary), COUNT(months* salary)  
FROM employee
WHERE months * salary = (SELECT MAX(months*salary) FROM employee)

문제 해석

먼저 month x salary값이 가장 큰 값을 찾아야 하기 때문에 MAX를 사용한다. 이때 서브쿼리를 이용하여 max값을 계산한 테이블을 하나 만들고 WHERE절을 이용하여 조건에 맞는 값들만 불러온다. 그후 MAX값을 counting하기 위해 COUNT를 사용한다.

---

Top Earners

문제

Table: Employee

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
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

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of a department and its name.

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

문제 풀이

# Write your MySQL query statement below

SELECT d.name AS Department , e.name AS Employee, e.salary AS Salary
FROM employee AS e
    INNER JOIN(
            SELECT departmentid,max(salary) AS max_salary
            FROM employee
            GROUP BY departmentid
            ) AS dp ON e.departmentId = dp.departmentID
            AND e.salary = dp.max_salary
    INNER JOIN department AS d on d.id = e.departmentID

문제 해석

골치좀 먹은 문제이다. 풀다 살짝 힘들어서 강의 해설을 살짝 참고하였다. 먼저 처음 INNER JOIN을 한 서브쿼리문을 보면 department로 groupby를 진행 한 후에 max salary값을 구해준다.
["departmentid", "max_salary"]
[[1, 90000], [2, 80000]]
하면 이런형식으로 table이 만들어진다. 이러한 table을 INNER JOIN을 이용하여 AS dp ON e.departmentId = dp.departmentID AND e.salary = dp.max_salary 2개의 조건에 맞춰 INNER JOIN을 진행한다.
그러면 서브쿼리에서 구한 max_salary와 기존 테이블이였던 employee에서의 max salary만 가져온다.
이때 department의 name도 가져와야하므로 INNER JOIN을 다시 한번 사용하여 진행해준다ㅓ.