Department vs. Company Salary: FAANG SQL Interview Question

문제 풀이 링크: https://datalemur.com/questions/sql-department-company-salary-comparison

FAANG SQL Interview Question | DataLemur

---

💼 부서별 평균 급여 비교 문제 요약

 

목적: 2024년 3월에 대해 각 부서별 평균 급여가 회사 전체 평균 급여보다 높은지 / 낮은지 / 같은지 비교

조건:

• 비교 결과는 'higher', 'lower', 'same' 중 하나로 표시
• 결과에는 다음 3개 컬럼 포함:
  1. department_id
  2. payment_date → MM-YYYY 형식의 문자열
  3. comparison 결과 (higher, lower, same)

---

1. 내 풀이

1. 2024년 3월 회사 전체 평균 급여 계산
   • salary 테이블에서 payment_date = '2024-03-31 00:00:00' 조건을 걸어 3월에 지급된 모든 급여의 평균 계산
2. 2024년 3월 부서별 평균 급여 계산
   • employee 테이블과 salary 테이블을 JOIN하여 부서별(department_id)로 3월 급여를 묶고, 평균 급여 계산
3. CROSS JOIN을 통해 회사 평균과 부서 평균 비교
   • 부서별 평균과 회사 평균을 CROSS JOIN하여 비교 기준을 만들고, CASE WHEN 구문으로 급여 수준 판단
4. 결과 포맷 정리
   • payment_date는 MM-YYYY 형식으로 문자열 포맷을 맞추고, 최종 결과로 다음 3개 컬럼 출력
     • department_id
     • payment_date
     • comparison (higher, lower, same)

WITH total_avg_salary AS (
  SELECT AVG(s.amount) AS total_avg
  FROM salary s
  WHERE s.payment_date = '2024-03-31 00:00:00'
),
department_avg_salary AS (
  SELECT 
    e.department_id,
    s.payment_date,
    AVG(s.amount) AS department_avg
  FROM employee e
  JOIN salary s ON e.employee_id = s.employee_id
  WHERE s.payment_date = '2024-03-31 00:00:00'
  GROUP BY e.department_id, s.payment_date
)
SELECT 
  das.department_id,
  TO_CHAR(das.payment_date, 'MM-YYYY') AS payment_date,
  CASE 
    WHEN das.department_avg < tas.total_avg THEN 'lower'
    WHEN das.department_avg = tas.total_avg THEN 'same'
    WHEN das.department_avg > tas.total_avg THEN 'higher'
  END AS comparison
FROM department_avg_salary das
CROSS JOIN total_avg_salary tas;

---

2. 정답 풀이

1. 2024년 3월 회사 전체 평균 급여 계산
   • salary 테이블에서 payment_date = '2024-03-31 00:00:00' 조건을 걸어 3월에 지급된 모든 급여의 평균 계산
   • payment_date를 남겨두어 이후 부서 평균과 JOIN 조건으로 활용
2. 2024년 3월 부서별 평균 급여 계산
   • employee 테이블과 salary 테이블을 JOIN하여 부서별(department_id)로 3월 급여를 묶고, 평균 급여 계산
   • payment_date를 포함하여 회사 평균과 정확히 JOIN 가능
3. 회사 평균과 부서 평균 JOIN 후 비교
   • payment_date를 기준으로 두 CTE(dept_avg, company_avg)를 INNER JOIN
4. 결과 포맷 정리
   • payment_date는 MM-YYYY 형식으로 문자열 포맷을 맞추고, 최종 결과로 다음 3개 컬럼 출력
     • department_id
     • payment_date
     • comparison (higher, lower, same)

WITH company_avg AS ( -- CTE from Step 1
  SELECT 
    payment_date,
    AVG(amount) AS co_avg_salary
  FROM salary
  WHERE payment_date = '03/31/2024 00:00:00'
  GROUP BY payment_date
)
, dept_avg AS ( -- CTE from Step 2
  SELECT
    e.department_id,
    s.payment_date,
    AVG(s.amount) AS dept_avg_salary
  FROM salary AS s
  JOIN employee AS e
    ON s.employee_id = e.employee_id
  WHERE s.payment_date = '03/31/2024 00:00:00'
  GROUP BY e.department_id, s.payment_date
)

SELECT
  d.department_id,
  TO_CHAR(d.payment_date, 'MM-YYYY') AS payment_date,
  CASE  
    WHEN d.dept_avg_salary > c.co_avg_salary THEN 'higher'
    WHEN d.dept_avg_salary < c.co_avg_salary THEN 'lower'
    ELSE 'same'
  END AS comparison
FROM dept_avg AS d
JOIN company_avg AS c
  ON d.payment_date = c.payment_date;

---

3. 비교

	내 풀이: CROSS JOIN 방식	정답 풀이: JOIN ON 방식
비교 방식	CROSS JOIN → 모든 부서에 회사 평균 한 줄 붙이기	INNER JOIN → 날짜를 기준으로 정확하게 연결
날짜 필드 처리	total_avg_salary는 날짜 정보 없이 한 줄만 존재	company_avg는 payment_date를 포함해 JOIN 기준 제공
다중 날짜 확장	어렵거나 수정 필요	쉽게 확장 가능 (월별 평균 비교도 가능)
가독성	단순하지만 JOIN 구조 아님	SQL 작성 관례에 더 익숙하고 직관적
실무 확장성	제한적	확장성과 재사용성 우수 ✅