[프로그래머스] ⭐⭐⭐⭐ 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (MySQL)

프로그래머스 사이트 링크

📌 문제 설명

회사의 사원 정보 테이블 HR_EMPLOYEES와 사원별 평가 테이블 HR_GRADE를 이용하여 사원별 성과금 정보를 조회하려고 합니다.

HR_EMPLOYEES 테이블

Column name	Type	Nullable	Description
EMP_NO	VARCHAR	FALSE	사번
EMP_NAME	VARCHAR	FALSE	성명
DEPT_ID	VARCHAR	FALSE	부서 ID
POSITION	VARCHAR	FALSE	직책
EMAIL	VARCHAR	FALSE	이메일
COMP_TEL	VARCHAR	FALSE	전화번호
HIRE_DATE	DATE	FALSE	입사일
SAL	NUMBER	FALSE	연봉

HR_GRADE 테이블

Column name	Type	Nullable	Description
EMP_NO	VARCHAR	FALSE	사번
YEAR	NUMBER	FALSE	연도
HALF_YEAR	NUMBER	FALSE	반기 (1: 상반기, 2: 하반기)
SCORE	NUMBER	FALSE	평가 점수

📌 문제

평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.

• 평가 등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.
• 평가 점수는 사원별 상반기, 하반기 평가 점수의 평균을 기준으로 합니다.
• 결과는 사번 기준으로 오름차순 정렬해주세요.

기준 점수	평가 등급	성과금 (연봉 기준)
96 이상	S	20%
90 이상	A	15%
80 이상	B	10%
이외	C	0%

📌 예시

HR_EMPLOYEES 테이블이 다음과 같고

EMP_NO	EMP_NAME	DEPT_ID	POSITION	SAL
2017002	정호식	D0001	팀장	65000000
2018001	김민석	D0001	팀원	60000000
2019001	김솜이	D0002	팀장	60000000
2020002	김연주	D0002	팀원	53000000
2020005	양성태	D0003	팀원	53000000

HR_GRADE 테이블이 다음과 같을 때

EMP_NO	YEAR	HALF_YEAR	SCORE
2017002	2022	1	92
2018001	2022	1	89
2019001	2022	1	94
2020002	2022	1	90
2020005	2022	1	92
2017002	2022	2	84
2018001	2022	2	89
2019001	2022	2	81
2020002	2022	2	91
2020005	2022	2	81

SQL을 실행하면 다음과 같이 출력되어야 합니다.

EMP_NO	EMP_NAME	GRADE	BONUS
2017002	정호식	B	6500000
2018001	김민석	B	6000000
2019001	김솜이	B	6000000
2020002	김연주	A	7950000
2020005	양성태	B	5300000

---

🧩 문제 구조 파악

본격적인 풀이 전에 데이터 흐름을 먼저 짚고 가겠습니다.

HR_GRADE 테이블
┌─────────┬──────┬───────────┬───────┐
│ EMP_NO  │ YEAR │ HALF_YEAR │ SCORE │
├─────────┼──────┼───────────┼───────┤
│ 2017002 │ 2022 │     1     │  92   │  ← 상반기
│ 2017002 │ 2022 │     2     │  84   │  ← 하반기
└─────────┴──────┴───────────┴───────┘
           사원 1명당 행이 2개 존재 (상/하반기)
           → AVG(SCORE) = (92 + 84) / 2 = 88.0 → 등급 B

이 구조를 이해하지 못하면 GROUP BY 없이 단순 JOIN만 했을 때 사원 1명이 2개 행으로 출력됩니다. 핵심은 “사원별 상·하반기 점수를 어떻게 1개 행으로 합치느냐” 입니다.

---

🚨 내가 틀렸던 과정 (디버깅 일지)

❌ 시도 1 - GROUP BY만 추가, AVG 누락

처음에는 아래처럼 작성했습니다.

SELECT he.EMP_NO, he.EMP_NAME,
    CASE WHEN SCORE >= 96 THEN "s"
         WHEN SCORE >= 90 THEN "a"
         WHEN SCORE >= 80 THEN "b"
         ELSE "c" END AS "GRADE",
    CASE WHEN SCORE >= 96 THEN 0.2 * he.SAL
         WHEN SCORE >= 90 THEN 0.15 * he.SAL
         WHEN SCORE >= 80 THEN 0.10 * he.SAL
         ELSE 0 * he.SAL END AS "BONUS"
FROM HR_EMPLOYEES AS he
JOIN HR_GRADE AS hg ON he.EMP_NO = hg.EMP_NO
GROUP BY he.EMP_NO, he.EMP_NAME
ORDER BY he.EMP_NO

에러 원인: GROUP BY를 쓰면 SELECT 절에 나오는 컬럼은 반드시 ① GROUP BY에 포함되거나 ② 집계 함수(AVG, SUM 등)로 감싸져야 합니다. SCORE는 둘 다 해당하지 않아 에러가 발생했습니다. 또한 사원 1명당 상·하반기 2개의 SCORE 행이 있는데, 이를 하나의 값으로 어떻게 처리할지 DB에게 알려주지 않은 것도 문제입니다.

⚠️ 핵심 규칙. GROUP BY 사용 시 SELECT 절의 컬럼은 반드시 GROUP BY 기준 컬럼이거나 집계 함수(AVG, SUM, MAX 등)로 묶여야 합니다.

❌ 시도 2 - AVG 추가, SAL 누락

SCORE를 AVG(SCORE)로 수정했습니다. 그런데 또 에러가 발생했습니다.

-- AVG 추가했는데 또 에러?
GROUP BY he.EMP_NO, he.EMP_NAME  -- he.SAL이 빠져 있음

에러 원인: SELECT 절에서 he.SAL을 사용했지만 GROUP BY에는 포함되지 않았습니다. he.SAL은 집계 함수로 감싸지도 않은 일반 컬럼이므로 DB 입장에서는 “어떤 SAL 값을 써야 하지?”라는 모호함이 생깁니다.

💡 Tip. GROUP BY에서 자주 하는 실수가 바로 이것입니다. SELECT 절에 있는 모든 비집계 컬럼을 GROUP BY에 넣었는지 한 번 더 확인하는 습관을 들이세요.

❌ 시도 3 - SAL 추가, 대소문자·따옴표 오류

GROUP BY he.EMP_NO, he.EMP_NAME, he.SAL로 수정하니 에러는 사라졌습니다. 그런데 오답 처리가 됐습니다.

-- 두 가지 문제가 숨어있음
CASE WHEN AVG(SCORE) >= 96 THEN "s"  -- ① 쌍따옴표 사용
                                      -- ② 소문자 's'

오답 원인 두 가지:

1. 쌍따옴표(") 문제: 표준 SQL에서 쌍따옴표는 컬럼명(식별자)을 감쌀 때 사용합니다. 문자열 데이터는 반드시 홑따옴표(')를 써야 합니다. MySQL은 기본 설정에서 관대하게 넘어가기도 하지만, 프로그래머스 채점 서버는 이를 엄격하게 검사합니다.

2. 소문자 등급 문제: 문제에서 요구하는 등급은 대문자 S, A, B, C입니다. 채점 서버는 대소문자를 엄격히 구분하므로 소문자로 제출하면 오답 처리됩니다.

⚠️ 주의. 따옴표 규칙 정리

• 문자열 데이터: 홑따옴표('S') 사용
• 컬럼명·테이블명 등 식별자: 백틱(`GRADE`) 또는 쌍따옴표("GRADE") 사용
• 이 규칙은 Oracle, PostgreSQL 등 다른 DB에서도 동일하게 적용됩니다.

---

💻 코드

ver(1) - GROUP BY + AVG() 직접 활용

핵심 아이디어: AVG(SCORE)를 CASE 문 안에 직접 사용하여 집계와 분기를 한 번에 처리합니다. SELECT 절에 쓰인 모든 비집계 컬럼(EMP_NO, EMP_NAME, SAL)을 GROUP BY에 포함시켜 에러를 방지합니다.

SELECT
    he.EMP_NO,
    he.EMP_NAME,
    -- AVG(SCORE): 상반기 + 하반기 점수의 평균을 집계 함수로 계산
    -- 홑따옴표(')와 대문자로 문자열 작성 (채점 서버 기준)
    CASE
        WHEN AVG(SCORE) >= 96 THEN 'S'
        WHEN AVG(SCORE) >= 90 THEN 'A'
        WHEN AVG(SCORE) >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,

    -- he.SAL은 사원마다 고정값이므로 AVG()로 묶지 않고
    -- 대신 GROUP BY에 포함시켜 비집계 컬럼 에러를 방지
    CASE
        WHEN AVG(SCORE) >= 96 THEN he.SAL * 0.2
        WHEN AVG(SCORE) >= 90 THEN he.SAL * 0.15
        WHEN AVG(SCORE) >= 80 THEN he.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES AS he
JOIN HR_GRADE AS hg ON he.EMP_NO = hg.EMP_NO  -- 사번 기준으로 두 테이블 연결
GROUP BY he.EMP_NO, he.EMP_NAME, he.SAL        -- SELECT의 비집계 컬럼 3개 모두 포함
ORDER BY he.EMP_NO;                            -- 사번 오름차순 정렬

💡 Tip. he.SAL은 사원 1명당 1개의 고정값이므로 GROUP BY에 추가해도 집계 결과에 영향을 주지 않습니다. 사원마다 연봉이 다르므로 AVG(SAL)로 묶지 않고, GROUP BY에 직접 포함시키는 것이 의미상 더 정확합니다.

ver(2) - CTE로 평균 점수 분리 (가독성 최우선) 💡

핵심 아이디어: AVG(SCORE)를 CTE에서 미리 계산해두면 메인 쿼리의 CASE 문에서 AVG(SCORE)를 반복 작성할 필요 없이 깔끔한 컬럼명(AVG_SCORE)으로 참조할 수 있습니다. 복잡한 쿼리일수록 CTE로 단계를 분리하는 것이 유지보수에 유리합니다.

-- Step 1. CTE: 사원별 상·하반기 평균 점수를 먼저 계산해 임시 테이블로 정의
WITH EMP_AVG_SCORE AS (
    SELECT
        EMP_NO,
        AVG(SCORE) AS AVG_SCORE  -- 상반기 + 하반기 점수의 평균
    FROM HR_GRADE
    GROUP BY EMP_NO              -- 사원별로 묶어서 집계
)

-- Step 2. 메인 쿼리: 사원 정보와 평균 점수를 JOIN하여 등급·성과금 계산
SELECT
    he.EMP_NO,
    he.EMP_NAME,
    -- 미리 구해둔 AVG_SCORE를 참조하므로 CASE 문이 훨씬 간결해짐
    CASE
        WHEN eas.AVG_SCORE >= 96 THEN 'S'
        WHEN eas.AVG_SCORE >= 90 THEN 'A'
        WHEN eas.AVG_SCORE >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN eas.AVG_SCORE >= 96 THEN he.SAL * 0.2
        WHEN eas.AVG_SCORE >= 90 THEN he.SAL * 0.15
        WHEN eas.AVG_SCORE >= 80 THEN he.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES he
JOIN EMP_AVG_SCORE eas ON he.EMP_NO = eas.EMP_NO  -- CTE와 사원 정보 연결
ORDER BY he.EMP_NO;

💡 Tip. ver(1) vs ver(2) 선택 기준

• ver(1)은 코드가 짧아 코딩 테스트에서 빠르게 작성할 때 유리합니다.
• ver(2)는 AVG(SCORE) 반복이 없어 가독성이 높고, CASE 조건이 복잡해질수록 유지보수가 쉽습니다. 실무에서는 ver(2) 스타일을 권장합니다.

ver(3) - 인라인 뷰(FROM 절 서브쿼리) 활용

핵심 아이디어: CTE와 논리적으로 완전히 동일하지만, WITH 절 없이 FROM 절 안에 서브쿼리를 직접 작성하는 방식입니다. MySQL 구버전처럼 CTE가 지원되지 않는 환경에서도 동작하며, 간단한 경우에는 오히려 더 간결하게 표현할 수 있습니다.

SELECT
    he.EMP_NO,
    he.EMP_NAME,
    CASE
        WHEN eas.AVG_SCORE >= 96 THEN 'S'
        WHEN eas.AVG_SCORE >= 90 THEN 'A'
        WHEN eas.AVG_SCORE >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN eas.AVG_SCORE >= 96 THEN he.SAL * 0.2
        WHEN eas.AVG_SCORE >= 90 THEN he.SAL * 0.15
        WHEN eas.AVG_SCORE >= 80 THEN he.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES he
JOIN (
    -- FROM 절 안에 서브쿼리를 직접 작성하여 인라인 뷰(가상 테이블)로 사용
    -- CTE의 WITH EMP_AVG_SCORE AS (...) 와 완전히 동일한 역할
    SELECT
        EMP_NO,
        AVG(SCORE) AS AVG_SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
) eas ON he.EMP_NO = eas.EMP_NO  -- 인라인 뷰(eas)와 사원 테이블 연결
ORDER BY he.EMP_NO;

ver(4) - 윈도우 함수 AVG() OVER() 활용

핵심 아이디어: GROUP BY 없이 윈도우 함수 AVG() OVER (PARTITION BY ...)를 사용해 각 행에 사원별 평균 점수를 계산합니다. JOIN 후 상·하반기 2개 행이 생기는데, DISTINCT로 중복을 제거합니다. 집계 없이 행을 유지한 채로 분석할 수 있다는 게 윈도우 함수의 핵심 특징입니다.

SELECT DISTINCT          -- JOIN으로 상/하반기 2개 행이 생기므로 중복 제거
    he.EMP_NO,
    he.EMP_NAME,
    -- AVG() OVER (PARTITION BY): GROUP BY 없이 사원별 평균을 각 행에 계산
    -- 일반 AVG()는 행을 묶어버리지만, 윈도우 함수는 행을 유지한 채로 계산
    CASE
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 96 THEN 'S'
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 90 THEN 'A'
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 96 THEN he.SAL * 0.2
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 90 THEN he.SAL * 0.15
        WHEN AVG(hg.SCORE) OVER (PARTITION BY he.EMP_NO) >= 80 THEN he.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES he
JOIN HR_GRADE hg ON he.EMP_NO = hg.EMP_NO  -- JOIN 시점에 사원당 2개 행 발생
ORDER BY he.EMP_NO;

⚠️ 주의. 윈도우 함수와 GROUP BY의 차이

• 일반 집계 함수(AVG())는 GROUP BY와 함께 사용하여 행을 묶어 1개로 줄입니다.
• 윈도우 함수(AVG() OVER())는 행을 묶지 않고 각 행에 집계 결과를 추가합니다. 그래서 DISTINCT로 중복을 별도로 제거해야 합니다.

---

📊 풀이법 비교 요약

	ver(1) GROUP BY + AVG	ver(2) CTE	ver(3) 인라인 뷰	ver(4) 윈도우 함수
가독성	⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐
코드 길이	짧음	보통	보통	보통
MySQL 버전	모든 버전	8.0+ 권장	모든 버전	8.0+
재사용성	❌	✅ CTE 재사용 가능	❌	❌
추천 상황	빠른 제출	실무·복잡한 쿼리	구버전 환경	윈도우 함수 연습

---

📝 이번 문제에서 배운 것

1. GROUP BY와 집계 함수는 항상 세트로 생각하기. SELECT에 나오는 컬럼이 GROUP BY에 없다면, 반드시 AVG(), SUM() 등 집계 함수로 감싸야 합니다.

2. SAL처럼 “사원마다 고정값인 컬럼”도 GROUP BY에 포함해야 한다. 의미상 변하지 않는 값이라도 DB 엔진은 이를 알 수 없으므로 명시적으로 지정해야 합니다.

3. SQL 문자열은 항상 홑따옴표('). 쌍따옴표는 식별자(컬럼명)에 사용하는 것이 표준이며, 다른 DBMS로 넘어갈 때도 홑따옴표 습관이 호환성을 높여줍니다.

4. 채점 서버는 대소문자를 엄격히 구분한다. 문제에서 S, A, B, C로 지정했다면 반드시 대문자로 출력해야 합니다.