[Group By 16~20] SQL 코딩테스트 고득점 Kit

16. 가격대 별 상품 개수 구하기 (LV2)
17. 언어별 개발자 분류하기 (LV4)
18. 조건에 맞는 사원 정보 조회하기 (LV 2)
19. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (LV 4)
20. 부서별 평균 연봉 조회하기 (LV 3)

16. 가격대 별 상품 개수 구하기 (LV2)

정답:

SELECT FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP
    ,  COUNT(PRODUCT_ID) AS PRODUCTS
  FROM PRODUCT
 GROUP
    BY FLOOR(PRICE / 10000) * 10000
 ORDER
    BY PRICE_GROUP ASC

17. 언어별 개발자 분류하기 (LV4)

정답:

SELECT CASE WHEN SUM(CASE WHEN s.NAME = 'Python' THEN 1 ELSE 0 END) > 0
             AND SUM(CASE WHEN s.CATEGORY = 'Front End' THEN 1 ELSE 0 END) > 0 THEN 'A'
            WHEN SUM(CASE WHEN s.NAME = 'C#' THEN 1 ELSE 0 END) > 0 THEN 'B'
            WHEN SUM(CASE WHEN s.CATEGORY = 'Front End' THEN 1 ELSE 0 END) > 0 THEN 'C'
        END AS GRADE
    ,  d.ID
    ,  d.EMAIL
  FROM DEVELOPERS AS d
  JOIN SKILLCODES AS s ON d.SKILL_CODE & s.CODE > 0
 GROUP
    BY d.ID, d.EMAIL
HAVING GRADE IS NOT NULL
 ORDER 
    BY GRADE, d.ID

문제 조건이 조금 복잡해서 좀 헤맸다 ㅜㅜ bit 연산자가 익숙하지 않아서 그런가

18. 조건에 맞는 사원 정보 조회하기 (LV 2)

정답:

WITH YEAR_SCORE AS (
    SELECT e.EMP_NO
        ,  e.EMP_NAME
        ,  e.POSITION
        ,  e.EMAIL
        ,  SUM(g.SCORE) AS SCORE
      FROM HR_DEPARTMENT AS d
      JOIN HR_EMPLOYEES AS e ON d.DEPT_ID = e.DEPT_ID
      JOIN HR_GRADE AS g ON e.EMP_NO = g.EMP_NO
     WHERE g.YEAR = 2022
     GROUP
        BY e.EMP_NO, e.EMP_NAME, e.POSITION, e.EMAIL
)
SELECT SCORE
    ,  EMP_NO
    ,  EMP_NAME
    ,  POSITION
    ,  EMAIL
  FROM YEAR_SCORE 
 WHERE SCORE = (SELECT MAX(SCORE)
                    FROM YEAR_SCORE)

19. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (LV 4)

정답:

WITH AVGSCORE AS(
    SELECT e.EMP_NO
        ,  e.EMP_NAME
        ,  SUM(g.SCORE)/2 AS AVG_SCORE
        ,  e.SAL
      FROM HR_EMPLOYEES AS e 
      JOIN HR_GRADE AS g ON e.EMP_NO = g.EMP_NO
     GROUP BY e.EMP_NO, e.EMP_NAME, e.SAL
) 
, GRADE AS (
    SELECT EMP_NO
        ,  EMP_NAME
        ,  CASE WHEN AVG_SCORE >= 96 THEN 'S'
                WHEN AVG_SCORE >= 90 THEN 'A'
                WHEN AVG_SCORE >= 80 THEN 'B'
                ELSE 'C'
           END AS GRADE
        ,  SAL
    FROM AVGSCORE
)
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
       END AS BONUS
  FROM GRADE

좀 쿼리문이 긴 것 같아서 다른 방법도 고민해 봄

SELECT e.EMP_NO
    ,  e.EMP_NAME
    ,  CASE WHEN AVG_SCORE >= 96 THEN 'S'
            WHEN AVG_SCORE >= 90 THEN 'A'
            WHEN AVG_SCORE >= 80 THEN 'B'
            ELSE 'C'
       END AS GRADE
    ,  CASE WHEN AVG_SCORE >= 96 THEN e.SAL * 0.2
            WHEN AVG_SCORE >= 90 THEN e.SAL * 0.15
            WHEN AVG_SCORE >= 80 THEN e.SAL * 0.1
            ELSE 0
       END AS BONUS
FROM (
    SELECT e.EMP_NO
        ,  e.EMP_NAME
        ,  e.SAL
        ,  SUM(g.SCORE)/2 AS AVG_SCORE
      FROM HR_EMPLOYEES AS e
      JOIN HR_GRADE AS g ON e.EMP_NO = g.EMP_NO
     GROUP 
        BY e.EMP_NO, e.EMP_NAME, e.SAL
) AS e

평균 점수에 따라 GRADE와 BONUS를 계산하기 때문에 중복은 불가피한 구조긴 하지만 WITH문 두개에 비하면 FROM절 서브 쿼리 하나가 나은 것 같기도 함!

20. 부서별 평균 연봉 조회하기 (LV 3)

정답:

SELECT d.DEPT_ID
    ,  d.DEPT_NAME_EN
    ,  ROUND(AVG(e.SAL), 0) AS AVG_SAL
  FROM HR_DEPARTMENT AS d
  JOIN HR_EMPLOYEES AS e ON d.DEPT_ID = e.DEPT_ID
 GROUP
    BY d.DEPT_ID, d.DEPT_NAME_EN
 ORDER
    BY AVG_SAL DESC