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

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

with avg_grade as(
select EMP_NO, avg(score) as a
from HR_GRADE 
group by EMP_NO
)

select 
    g.EMP_NO, 
    e.EMP_NAME,
    CASE
        WHEN g.a >= 96 THEN 'S'
        WHEN g.a >= 90 THEN 'A'
        WHEN g.a >= 80 THEN 'B'
        ELSE 'C'
    END as GRADE,
    CASE
        WHEN g.a >= 96 THEN e.SAL*0.2
        WHEN g.a >= 90 THEN e.SAL*0.15
        WHEN g.a >= 80 THEN e.SAL*0.1
        ELSE 0
    END as BONUS
from avg_grade g, HR_EMPLOYEES e
where g.EMP_NO = e.EMP_NO
order by EMP_NO

==
강사님 답

WITH avg_score AS (
SELECT
    EMP_NO,
    AVG(SCORE) AS avg_score
    FROM HR_GRADE
    WHERE YEAR = 2022
    GROUP BY EMP_NO
)
SELECT
    e.EMP_NO,
    e.EMP_NAME,
    CASE
        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
        WHEN a.avg_score >= 96 THEN e.SAL * 0.2
        WHEN a.avg_score >= 90 THEN e.SAL * 0.15
        WHEN a.avg_score >= 80 THEN e.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES AS e
INNER JOIN avg_score AS a
    ON e.EMP_NO = a.EMP_NO
ORDER BY e.EMP_NO;