문제: 지역별 직원 1인당 평균 매출 비교 분석

한 회사는 지역 단위로 직원들의 매출 성과를 분석하고자 한다.
데이터는 다음과 같은 테이블 구조로 관리된다.

📋 테이블 구조

regions (
  id INT PRIMARY KEY,
  name VARCHAR(50)
)

states (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  regionId INT
)

employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  stateId INT
)

sales (
  id INT PRIMARY KEY,
  amount INT,
  employeeId INT
)

📌 요구사항

각 지역(region)에 대해 다음 정보를 조회하라.

1. 지역 이름

2. 해당 지역의 직원 1인당 평균 매출

   • 평균 매출 = 지역 총 매출 / 지역 내 직원 수
   • 직원이 없거나, 직원은 있으나 매출이 없는 경우 평균 매출은 0으로 처리한다

3. 전체 지역 중 가장 높은 평균 매출과 해당 지역 평균 매출의 차이

⚠️ 주의사항

• 직원이 없는 지역도 결과에 반드시 포함되어야 한다
• 직원이 있어도 매출이 없는 경우를 고려해야 한다
• 결과는 모든 지역에 대해 반환되어야 한다

---

✅ 정답 쿼리

WITH region_avgs AS (
  SELECT
    r.name AS region_name,
    COALESCE(
      SUM(sl.amount) * 1.0
      / NULLIF(COUNT(DISTINCT e.id), 0),
      0
    ) AS avg_amount
  FROM regions r
  LEFT JOIN states st
    ON r.id = st.regionId
  LEFT JOIN employees e
    ON st.id = e.stateId
  LEFT JOIN sales sl
    ON sl.employeeId = e.id
  GROUP BY r.name
)
SELECT
  region_name,
  avg_amount,
  (SELECT MAX(avg_amount) FROM region_avgs) - avg_amount AS diff_highest_avg
FROM region_avgs;

---

🧠 단계별 해설

1️⃣ 기준 테이블은 반드시 regions이다

이 문제의 핵심 조건은 다음 문장이다.

직원이 없거나 매출이 없는 지역도 결과에 포함되어야 한다

즉, 결과 집합에서 절대 사라지면 안 되는 엔티티는 regions이다.
따라서 JOIN 방향은 반드시 다음 구조여야 한다.

regions
  LEFT JOIN states
    LEFT JOIN employees
      LEFT JOIN sales

INNER JOIN을 사용하면 직원이나 주(state)가 없는 지역은 결과에서 탈락한다.
이는 요구사항 위반이다.

---

2️⃣ 평균 매출 계산 로직

SUM(sl.amount) / COUNT(DISTINCT e.id)

이 수식은 다음 의미를 가진다.

• 분자: 해당 지역에서 발생한 총 매출
• 분모: 해당 지역에 속한 직원 수

하지만 이 계산에는 두 가지 위험이 존재한다.

❌ 위험 1: 직원이 없는 지역

COUNT(DISTINCT e.id) = 0

→ 0으로 나누기 발생
→ SQL 에러 또는 NULL 반환

❌ 위험 2: 매출이 없는 직원만 있는 지역

SUM(sl.amount) = NULL

→ 평균이 NULL이 됨

---

3️⃣ NULLIF와 COALESCE의 역할

이를 방지하기 위해 다음과 같이 처리한다.

SUM(sl.amount) * 1.0
/ NULLIF(COUNT(DISTINCT e.id), 0)

NULLIF(x, 0)

• x가 0이면 NULL 반환
• 0으로 나누는 상황을 원천 차단한다

COALESCE(..., 0)

• 결과가 NULL이면 0으로 대체
• 직원이 없거나 매출이 없어도 평균을 0으로 명시

즉,

직원이 없거나, 매출이 전혀 없는 지역의 평균 매출은 0이다

라는 요구사항을 정확히 구현한다.

---

4️⃣ COUNT(DISTINCT e.id)를 사용한 이유

직원은 여러 건의 매출을 가질 수 있다.
만약 COUNT(e.id)를 사용하면 매출 건수만큼 직원이 중복 계산된다.

COUNT(DISTINCT e.id)

→ 지역 내 고유한 직원 수 계산
→ 평균 정의에 정확히 부합한다.

---

5️⃣ 최고 평균 매출과의 차이 계산

(SELECT MAX(avg_amount) FROM region_avgs) - avg_amount

• 모든 지역 중 가장 높은 평균 매출을 서브쿼리로 계산
• 현재 지역의 평균 매출을 차감
• 상대적 성과 비교 가능

CTE(region_avgs)를 사용했기 때문에 중복 계산 없이 깔끔하게 표현된다.

---

6️⃣ 평가 포인트

이 문제는 단순한 집계 문제가 아니다.
핵심은 다음 질문이다.

“결과에서 반드시 살아남아야 하는 테이블은 무엇인가?”

정답 사고 흐름은 다음과 같다.

1. 모든 지역을 보여야 한다 → regions 기준
2. 직원/매출 유무와 상관없이 유지 → LEFT JOIN
3. 평균 계산 시 0명 / NULL 보호
4. 최고값과 비교하는 파생 지표 계산

---

✍️ 한 줄 소감

지역 단위 평균 매출 분석에서는 JOIN 방향이 계산식보다 중요하며, 직원이 없는 경우를 고려해 LEFT JOIN과 NULLIF, COALESCE 처리가 필수적이다.