[Programmers] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

문제 링크

정답

-- HR_DEPARTMENT: DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION
-- HR_EMPLOYEES: EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL
-- HR_GRADE: EMP_NO, YEAR, HALF_YEAR, SCORE

WITH TOTAL_GRADE AS (
    SELECT EMP_NO,
        CASE 
            WHEN SUM(SCORE) >= 192 THEN 'S'
            WHEN SUM(SCORE) >= 180 THEN 'A'
            WHEN SUM(SCORE) >= 160 THEN 'B'
            ELSE 'C'
        END AS GRADE 
    FROM HR_GRADE
    GROUP BY EMP_NO
)


SELECT E.EMP_NO, E.EMP_NAME, G.GRADE,
    CASE
        WHEN G.GRADE = 'S' THEN E.SAL * 0.2
        WHEN G.GRADE = 'A' THEN E.SAL * 0.15
        WHEN G.GRADE = 'B' THEN E.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM TOTAL_GRADE AS G
JOIN HR_EMPLOYEES AS E ON G.EMP_NO = E.EMP_NO
ORDER BY 1;

✅ Point

• CTE 와 JOIN 활용
  • CTE를 사용해 등급 구하기
  • HR_EMPLOYEES tablerhk join하여 최종 출력 구성