[SQL 달리기반 레벨 4] 💸 가장 높은 월급을 받는 직원은?

---

문제 : 💸가장 높은 월급을 받는 직원은?

1. 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

기대사항 :

2. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

기대사항:

---

1. 개념 배우기

WITH AS 문

- 공통 테이블 표현식(CTE, Common Table Expression) 을 정의할 때 사용되며, 서브쿼리를 깔끔하게 정리하고 재사용할 수 있게 해준다.

- WITH 안에서 일시적인 테이블(CTE) 을 만들고, 메인 SELECT 문에서 이를 참조하는 방식.

- 복잡한 쿼리를 쪼개어 가독성을 높이고, 동일한 서브쿼리를 여러 번 실행하는 비효율을 방지.

WITH CTE명 AS (
    서브쿼리
)
SELECT * FROM CTE명;

2. 알고리즘

1) 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

1단계: Department를 기준으로 Salary가 높은 값 max 해주기.

2단계: Only full group by 조건을 피하기 위해, 두번째 Subquery에서 Top_Earner 추출

3단계: Name 순으로 정렬 후, Name Department Salary Top_Earner Top_Salary 뽑기

2) 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회

1단계: 부서 별 평균월급 계산

2단계: 평균 월급이 높은 열 선택

 

3. SQL 코드

1) 1번 문제

1단계: Department를 기준으로 Salary가 높은 값 max 해주기.

WITH DepartmentMax AS (
    SELECT Department, Max(Salary) AS Top_Salary
    FROM Employees
    GROUP BY Department)

2단계: Only full group by 조건을 피하기 위해, 두번째 Subquery에서 Top_Earner 추출

WITH DepartmentMax AS (
    SELECT Department, Max(Salary) AS Top_Salary
    FROM Employees
    GROUP BY Department
)
select d.Department, e.Name as Top_Earner, d.Top_Salary
from DepartmentMax d join employees e on d.Department = e.Department
where d.Top_Salary = e.Salary

3단계: Name 순으로 정렬 후, Name Department Salary Top_Earner Top_Salary 뽑기

WITH DepartmentMax AS (
    SELECT Department, Max(Salary) AS Top_Salary
    FROM Employees
    GROUP BY Department
), NameExtra as (
select d.Department, e.Name as Top_Earner, d.Top_Salary
from DepartmentMax d join employees e on d.Department = e.Department
where d.Top_Salary = e.Salary )
SELECT e.Name, e.Department, e.Salary, n.Top_Earner, n.Top_Salary
FROM Employees e
JOIN NameExtra n ON e.Department = n.Department
order by e.Name

2) 2번 문제

1단계: 부서 별 평균월급 계산

with DepartmentMaxAVG as (
select Department, AVG(Salary) as Avg_Salary
from employees
group by 1
)

2단계: 평균 월급이 높은 열 선택

with DepartmentMaxAVG as (
select Department, AVG(Salary) as Avg_Salary
from employees
group by 1
)
select Department, Avg_Salary
from DepartmentMaxAVG 
order by Avg_Salary desc 
limit 1