[프로그래머스 SQL 코딩테스트 연습] Lv4. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (MySQL)

🤔 문제

`HR_DEPARTMENT` 테이블은 회사의 부서 정보를 담은 테이블입니다. `HR_DEPARTMENT` 테이블의 구조는 다음과 같으며 `DEPT_ID`, `DEPT_NAME_KR`, `DEPT_NAME_EN`, `LOCATION`은 각각 부서 ID, 국문 부서명, 영문 부서명, 부서 위치를 의미합니다.

 

`HR_EMPLOYEES` 테이블은 회사의 사원 정보를 담은 테이블입니다. `HR_EMPLOYEES` 테이블의 구조는 다음과 같으며 `EMP_NO`, `EMP_NAME`, `DEPT_ID`, `POSITION`, `EMAIL`, `COMP_TEL`, `HIRE_DATE`, `SAL`은 각각 사번, 성명, 부서 ID, 직책, 이메일, 전화번호, 입사일, 연봉을 의미합니다.

 

`HR_GRADE` 테이블은 2022년 사원의 평가 정보를 담은 테이블입니다. `HR_GRADE`의 구조는 다음과 같으며 `EMP_NO`, `YEAR`, `HALF_YEAR`, `SCORE`는 각각 사번, 연도, 반기, 평가 점수를 의미합니다.

 

`HR_DEPARTMENT`, `HR_EMPLOYEES`, `HR_GRADE` 테이블을 이용해 사원별 성과금 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.

평가등급의 컬럼명은 `GRADE`로, 성과금의 컬럼명은 `BONUS`로 해주세요.

결과는 사번 기준으로 오름차순 정렬해주세요.

 

 

예시

`HR_DEPARTMENT` 테이블이 다음과 같고

 

`HR_EMPLOYEES` 테이블이 다음과 같고

 

`HR_GRADE` 테이블이 다음과 같을 때

 

다음과 같이 사원별 성과금 정보를 출력해야 합니다.

 

 

 

 

 

 

 

 

 

---

💻 나의 풀이

WITH EMP_GRADE AS (
    SELECT e.EMP_NO, e.EMP_NAME, e.DEPT_ID, e.SAL,
        CASE
            WHEN AVG(g.SCORE) >= 96 THEN 'S'
            WHEN (AVG(g.SCORE) >= 90 AND AVG(g.SCORE) < 96) THEN 'A'
            WHEN (AVG(g.SCORE) >= 80 AND AVG(g.SCORE) < 90) THEN 'B'
            WHEN AVG(g.SCORE) < 80 THEN 'C'
            ELSE NULL
        END AS GRADE
    FROM HR_EMPLOYEES e
    JOIN HR_GRADE g ON e.EMP_NO=g.EMP_NO
    -- WHERE g.HALF_YEAR = 2 -- 정확하게 어떤 반기의 등급인지(ex. 상반기와 하반기의 평균인지) 모르겠음..
    GROUP BY 1
)
SELECT EMP_NO, EMP_NAME, GRADE,
    CASE
        WHEN GRADE = 'S' THEN SAL * 0.2
        WHEN GRADE = 'A' THEN SAL * 0.15
        WHEN GRADE = 'B' THEN SAL * 0.1
        WHEN GRADE = 'C' THEN SAL * 0
        ELSE NULL
    END AS BONUS
FROM EMP_GRADE
ORDER BY 1;

 

문제 설명이 불친절했다. 주어진 예시로 판단했을 때는 하반기 평가점수를 기반으로 매긴 등급인 줄 알았는데, 이에 맞게 코드를 작성하니 오답으로 판정 받았다. 그래서 상반기와 하반기의 평가점수 ‘평균’을 기반으로 등급을 매겨본 결과 정답으로 판정 받았다. 문제를 푸는 데 있어서 꼭 필요한 정보인 것 같아, 혹시 이 글을 보시는 분들 중 해당 문제를 풀고있다면 참고하길..!

 

그리고 문제 풀이 굳이 필요 없는 테이블(부서 정보 테이블)도 나와있어서 오히려 문제 파악에 시간을 잡아먹었다.

 

 

 

결과

 

 

 

 

 

 

---

💡 다른 풀이

1. 평가등급과 성과금을 계산하는 서브쿼리를 각각 만들어 조회하는 방법

SELECT 
	e.EMP_NO, 
    e.EMP_NAME, 
	-- 평가등급 계산 (서브쿼리 활용)
	(CASE
    	WHEN AVG(g.SCORE) >= 96 THEN 'S'
    	WHEN AVG(g.SCORE) >= 90 THEN 'A'
    	WHEN AVG(g.SCORE) >= 80 THEN 'B'
    	ELSE 'C' 
     END) AS GRADE, 
	 -- 성과금 계산
	(CASE
    	WHEN AVG(g.SCORE) >= 96 THEN e.SAL*0.2
    	WHEN AVG(g.SCORE) >= 90 THEN e.SAL*0.15
    	WHEN AVG(g.SCORE) >= 80 THEN e.SAL*0.1
    	ELSE 0 
     END) AS BONUS
FROM HR_EMPLOYEES e
JOIN HR_GRADE g ON e.EMP_NO = g.EMP_NO
GROUP BY 1
ORDER BY 1;

 

 

 

 

 

 

2. WITH절을 사용하여 메인쿼리에서 WITH절의 평가 등급, 성과금 정보를 조회하는 방법

WITH EMP_GRADE AS(
	SELECT
    	e.EMP_NO,
        e.EMP_NAME,
        e.SAL,
        (CASE -- 평가 점수에 따른 평가 등급
        	WHEN AVG(g.SCORE) >= 96 THEN 'S'
            WHEN AVG(g.SCORE) >= 90 THEN 'A'
            WHEN AVG(g.SCORE) >= 80 THEN 'B'
            ELSE 'C'
         END) AS GRADE,
        (CASE -- 평가 점수에 따른 성과급 비율
        	WHEN AVG(g.SCORE) >= 96 THEN 0.2
            WHEN AVG(g.SCORE) >= 90 THEN 0.15
            WHEN AVG(g.SCORE) >= 80 THEN 0.1
            ELSE 0
        END) AS BONUS_PERC
    FROM HR_EMPLOYEES e
    JOIN HR_GRADE g ON e.EMP_NO = g.EMP_NO
    GROUP BY 1 -- 사번별로 그룹화
)
SELECT EMP_NO, EMP_NAME, GRADE, (SAL * BONUS_PERC) AS BONUS
FROM EMP_GRADE
ORDER BY 1;

 

 

 

 

 

방법 1이 제일 깔끔한 것 같다.

성능 측면에서는 방법 2가 좋을수도

 

 

---

🔗 References

[다른풀이 1 참고]

https://velog.io/@dkan9634/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4SQL-%EC%97%B0%EA%B0%84-%ED%8F%89%EA%B0%80%EC%A0%90%EC%88%98%EC%97%90-%ED%95%B4%EB%8B%B9%ED%95%98%EB%8A%94-%ED%8F%89%EA%B0%80-%EB%93%B1%EA%B8%89-%EB%B0%8F-%EC%84%B1%EA%B3%BC%EA%B8%88-%EC%A1%B0%ED%9A%8C%ED%95%98%EA%B8%B0-ae8s98xu

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

 

 

 

 

[다른풀이 2 참고]

https://o-s-o-jjj.tistory.com/24

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