[Today's Query] 부서 평균 연봉보다 연봉이 높은 사원을 조회 (2025/01/06)

문제

최신 연봉이 부서 최신 연봉의 평균보다 높은 사원을 조회하세요.

WITH LatestSalaries AS (
    SELECT 
        emp_no,
        salary,
        ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS rn
    FROM 
        salaries
),
avgSalary AS (
    SELECT
        b.dept_no,
        AVG(a.salary) AS avg_salary
    FROM
        (SELECT emp_no, salary FROM LatestSalaries WHERE rn = 1) AS a
    JOIN
        dept_emp AS b ON a.emp_no = b.emp_no
    GROUP BY
        b.dept_no
)
SELECT
    q.emp_no,
    CONCAT(q.first_name, ' ', q.last_name) AS emp_name,
    q.gender,
    q.hire_date,
    r.dept_name,
    w.salary,
    p.avg_salary AS '부서 평균 연봉'
FROM
    employees AS q
JOIN
    (SELECT emp_no, salary FROM LatestSalaries WHERE rn = 1) AS w ON q.emp_no = w.emp_no
JOIN
    dept_emp AS e ON q.emp_no = e.emp_no
JOIN
    departments AS r ON e.dept_no = r.dept_no
JOIN
    avgSalary AS p ON r.dept_no = p.dept_no
WHERE
    w.salary > p.avg_salary
ORDER BY r.dept_no, w.salary DESC;

💡 ROW_NUMBER()
결과 집합의 파티션 내에서 각 행에 순차적인 정수를 할당하는 함수