[Group By 1~5] SQL 코딩테스트 고득점 Kit

1. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 (LV3)
2. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (LV3)
3. 저자 별 카테고리 별 매출액 집계하기 (LV4)
4. 식품분류별 가장 비싼 식품의 정보 조회하기 (LV4)
5. 즐겨찾기가 가장 많은 식당 정보 출력하기 (LV3)

1. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 (LV3)

정답:

SELECT MONTH(START_DATE) AS MONTH
    ,  CAR_ID
    ,  COUNT(HISTORY_ID) AS RECORDS
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
   AND CAR_ID IN (SELECT CAR_ID
                  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                  WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                  GROUP BY CAR_ID
                  HAVING COUNT(HISTORY_ID) >= 5)
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC

기간 조건은 메인 쿼리 & 서브 쿼리 둘 다 들어가있어야 함!

2. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (LV3)

정답:

SELECT CAR_ID
     , CASE 
         WHEN SUM(CASE 
                     WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1 
                     ELSE 0 
                  END) > 0 
           THEN '대여중'
         ELSE '대여 가능'
       END AS AVAILABILITY
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 GROUP 
    BY CAR_ID
 ORDER 
    BY CAR_ID DESC

오답 쿼리:

SELECT CAR_ID
    ,  CASE WHEN MAX(START_DATE) <= '2022-10-16' AND MAX(END_DATE) >= '2022-10-16' THEN '대여중'
            ELSE '대여 가능' END AS AVAILABITY
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 GROUP
    BY CAR_ID
 ORDER
    BY CAR_ID DESC

처음에 문제를 접근할 때 CAR_ID 별로 가장 최근 대여 이력만 체크하면 될 것이라고 생각해서 MAX, MIN 함수를 써서 풀었지만 오답!

• 여러 대여 기록 중 하나라도 10/16에 포함되면 대여중인데, 이 쿼리는 가장 마지막 대여 기록만 검사함
• 그래서 중간에 대여된 기록이 있어도 놓칠 수 있음 😲
• 정답쿼리처럼 CASE WHEN 으로 모든 기록을 고려해줘야했던 문제!

3. 저자 별 카테고리 별 매출액 집계하기 (LV4)

정답:

SELECT a.AUTHOR_ID
    ,  a.AUTHOR_NAME
    ,  b.CATEGORY
    ,  SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
  FROM BOOK AS b
  JOIN AUTHOR AS a ON b.AUTHOR_ID = a.AUTHOR_ID
  JOIN BOOK_SALES AS bs ON b.BOOK_ID = bs.BOOK_ID
 WHERE bs.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
 GROUP
    BY a.AUTHOR_ID, b.CATEGORY
 ORDER
    BY a.AUTHOR_ID ASC, b.CATEGORY DESC

4. 식품분류별 가장 비싼 식품의 정보 조회하기 (LV4)

정답:

SELECT CATEGORY
    ,  PRICE AS MAX_PRICE
    ,  PRODUCT_NAME
  FROM (
    SELECT CATEGORY
        ,  PRICE
        ,  PRODUCT_NAME
        ,  RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RNK
      FROM FOOD_PRODUCT
     WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    ) AS RANKED
WHERE RNK = 1
ORDER BY MAX_PRICE DESC

오답 쿼리:

SELECT CATEGORY
    ,  MAX(PRICE) AS MAX_PRICE
    ,  PRODUCT_NAME
  FROM FOOD_PRODUCT
 GROUP
    BY CATEGORY
HAVING CATEGORY = '과자' 
    OR CATEGORY = '국' 
    OR CATEGORY = '김치'
    OR CATEGORY = '식용유'
 ORDER
    BY MAX(PRICE) DESC

이렇게 쿼리를 작성하면 GROUP BY로 집계는 됐지만, PRODUCT_NAME은 그룹에서 임의의 행 하나가 선택됨
→ 즉, MAX(PRICE)가 아닌 다른 제품의 이름이 나올 수 있음 ⭐️

• 정확히 표현하고 싶을 때는 윈도우 함수를 사용해야 함

• 카테고리도 일일이 쓰지말고 IN 함수를 쓰는게 더 효율적

5. 즐겨찾기가 가장 많은 식당 정보 출력하기 (LV3)

정답:

SELECT FOOD_TYPE
    ,  REST_ID
    ,  REST_NAME
    ,  FAVORITES
  FROM (
    SELECT FOOD_TYPE
        ,  REST_ID
        ,  REST_NAME
        ,  FAVORITES
        ,  RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS FAV_RNK
      FROM REST_INFO
    ) AS MAX_FAVS
 WHERE FAV_RNK = 1
 ORDER
   BY FOOD_TYPE DESC

4번이랑 비슷하게 풀면 됐던 문제