[SQL 문제 풀이] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

-- CTE를 사용하여 사원별 2022년도 평균 점수를 계산
WITH AVERAGE_SCORE AS (
    SELECT 
        G.EMP_NO,                -- 사번
        AVG(G.SCORE) AS AVG_SCORE -- 각 사원의 평가 점수 평균
    FROM 
        HR_GRADE G
    WHERE 
        G.YEAR = 2022            -- 2022년도 데이터만 선택
    GROUP BY 
        G.EMP_NO                 -- 사번별로 그룹화하여 평균 점수 계산
)
-- 사원 정보, 평가 등급, 성과금 정보를 조회
SELECT 
    E.EMP_NO,
    E.EMP_NAME,
    CASE 
        -- 평균 점수에 따라 평가 등급(GRADE)을 계산
        WHEN A.AVG_SCORE >= 96 THEN 'S'
        WHEN A.AVG_SCORE >= 90 THEN 'A'
        WHEN A.AVG_SCORE >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE 
        -- 평가 등급에 따라 성과금(BONUS)을 계산
        WHEN A.AVG_SCORE >= 96 THEN E.SAL * 0.2 -- S 등급: 연봉의 20%
        WHEN A.AVG_SCORE >= 90 THEN E.SAL * 0.15 -- A 등급: 연봉의 15%
        WHEN A.AVG_SCORE >= 80 THEN E.SAL * 0.1 -- B 등급: 연봉의 10%
        ELSE 0                                 -- C 등급: 성과금 없음
    END AS BONUS
FROM 
    HR_EMPLOYEES E
JOIN 
    AVERAGE_SCORE A            -- CTE와 조인
ON 
    E.EMP_NO = A.EMP_NO        -- EMP_NO를 기준으로 조인
ORDER BY 
    E.EMP_NO ASC;              -- 사번 기준 오름차순 정렬