[MY SQL] WITH절을 활용하여 평가등급 및 성과금 조회

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] : 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/284528

문제(일부): 평가등급과 성과금, 평가자 정보 조회
평가등급은 상,하반기 평균점수, 성과금 비율은 평가등급에 따라 부여됨

1. WITH절 사용

1) 상, 하반기 평균점수를 구하는 WITH절 생성
2) CASE WHEN 구문으로 1)의 평균 점수에 따라 평가등급과 성과금 비율 부여
3) 2)의 with절과 평가자 테이블을 JOIN하여 정보조회

WITH AVG_SCORE AS(
    SELECT EMP_NO, AVG(SCORE) AS AVG_SCORE
    FROM HR_GRADE 
    GROUP BY EMP_NO )
    
    , GRADE AS(
    SELECT EMP_NO
        , CASE
                WHEN AVG_SCORE >= 96 THEN "S"
                WHEN AVG_SCORE >= 90 THEN "A"
                WHEN AVG_SCORE >= 80 THEN "B"
                ELSE "C"
            END GRADE
        , CASE
                WHEN AVG_SCORE >= 96 THEN 0.2
                WHEN AVG_SCORE >= 90 THEN 0.15
                WHEN AVG_SCORE >= 80 THEN 0.1
                ELSE 0
            END BONUS_PER
    FROM AVG_SCORE) 
SELECT G.EMP_NO 
        , HE.EMP_NAME
        , G.GRADE
        , HE.SAL * G.BONUS_PER AS BONUS
FROM GRADE G INNER JOIN HR_EMPLOYEES HE
    ON G.EMP_NO = HE.EMP_NO
ORDER BY G.EMP_NO

 

2. 더 깔끔한 WITH절 사용풀이

1) 1번의 1,2번 내용을 1개의 WITH절에서 진행
     CASE WHEN구문으로 등급과 등급별 성과금 비율 부여
     평가자 테이블과 JOIN하여 평가자 정보 확인
2) 메인쿼리에서 WITH절의 정보를 조회

WITH EmployeeGrades AS (
    SELECT 
        HE.EMP_NO,
        HE.EMP_NAME,
        HE.SAL,
        AVG(HG.SCORE) AS AVG_SCORE,
        CASE
            WHEN AVG(HG.SCORE) >= 96 THEN 'S'
            WHEN AVG(HG.SCORE) >= 90 THEN 'A'
            WHEN AVG(HG.SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END AS GRADE,
        CASE
            WHEN AVG(HG.SCORE) >= 96 THEN 0.2
            WHEN AVG(HG.SCORE) >= 90 THEN 0.15
            WHEN AVG(HG.SCORE) >= 80 THEN 0.1
            ELSE 0
        END AS BONUS_PER
    FROM HR_EMPLOYEES HE
    JOIN HR_GRADE HG ON HE.EMP_NO = HG.EMP_NO
    GROUP BY HE.EMP_NO, HE.EMP_NAME, HE.SAL
)
SELECT 
    EG.EMP_NO,
    EG.EMP_NAME,
    EG.GRADE,
    EG.SAL * EG.BONUS_PER AS BONUS
FROM EmployeeGrades EG
ORDER BY EG.EMP_NO;