Leetcode - 615. Average Salary: Departments VS Company

문제

Loading...

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

LeetCode

정답

temp 테이블을 하나 만들고, 여기서 구한 평균을 이용해 CASE 문을 사용했다.

SELECT Salary.pay_month, department_id,
        CASE
            WHEN amount > average_amount THEN "higher"
            WHEN amount = average_amount THEN "same"
            ELSE "lower"
        END AS comparison
FROM (
    SELECT DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, AVG(amount) AS amount, department_id
    FROM Salary
    JOIN Employee ON Salary.employee_id = Employee.employee_id
    GROUP BY department_id, pay_month
) AS Salary
JOIN (
    SELECT DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, AVG(amount) as average_amount
    FROM Salary
    GROUP BY pay_month
    ) AS Monthly ON Salary.pay_month = Monthly.pay_month