[Join 1~6] SQL 코딩테스트 고득점 Kit

1. 주문량이 많은 아이스크림들 조회하기 (LV 4)
2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (LV 4)
3. 5월 식품들의 총매출 조회하기 (LV 4)
4. 조건에 맞는 도서와 저자 리스트 출력하기 (LV 2)
5. 그룹별 조건에 맞는 식당 목록 출력하기 (LV 4)
6. 없어진 기록 찾기 (LV 3)

1. 주문량이 많은 아이스크림들 조회하기 (LV 4)

정답:

WITH TOTAL_ORDER AS (
    SELECT FLAVOR
        ,  SUM(TOTAL_ORDER) AS TOT_ORDER
      FROM FIRST_HALF
     GROUP
        BY FLAVOR
    UNION ALL
    SELECT FLAVOR
        ,  SUM(TOTAL_ORDER) AS TOT_ORDER
      FROM JULY
     GROUP
        BY FLAVOR
)
, RANKED_FLAVOR AS (
    SELECT FLAVOR
        ,  SUM(TOT_ORDER)
        ,  ROW_NUMBER() OVER(ORDER BY SUM(TOT_ORDER) DESC) AS RNK 
      FROM TOTAL_ORDER
     GROUP
        BY FLAVOR
)
SELECT FLAVOR
  FROM RANKED_FLAVOR
 WHERE RNK <= 3

2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (LV 4)

정답:

SELECT c.CAR_ID
    ,  c.CAR_TYPE
    ,  ROUND(c.DAILY_FEE * 30 * (1 - (p.DISCOUNT_RATE / 100)), 0) AS FEE
  FROM CAR_RENTAL_COMPANY_CAR AS c
LEFT
  JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS h ON c.CAR_ID = h.CAR_ID
LEFT
  JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS p ON c.CAR_TYPE = p.CAR_TYPE
 WHERE (c.CAR_TYPE = '세단' OR c.CAR_TYPE = 'SUV')
   AND p.DURATION_TYPE = '30일 이상'
   AND c.CAR_ID NOT IN (SELECT CAR_ID
                      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE START_DATE <= '2022-11-30' 
                       AND END_DATE >= '2022-11-01')
   AND c.DAILY_FEE * 30 * (1 - (p.DISCOUNT_RATE / 100)) >= 500000
   AND c.DAILY_FEE * 30 * (1 - (p.DISCOUNT_RATE / 100)) < 2000000
 GROUP
    BY c.CAR_ID, c.CAR_TYPE
 ORDER
    BY FEE DESC, c.CAR_TYPE, c.CAR_ID DESC

쿼리 설명:

• 조건 ①: 세단 또는 SUV 차량만
• 조건 ②: 할인 조건 중 '30일 이상'에 해당하는 할인율만 적용
• 조건 ③: 2022년 11월에 이미 대여 기록이 있는 차량은 제외 (NOT IN으로 제외 처리)
  📌 이 조건이 핵심! 11월에 예약 불가능한 차는 제외돼야 하니까
• 조건 ④: 할인 후 금액이 50만 원 이상, 200만 원 미만인 차량만 필터링

개인적으로 12문제 중 제일 어려운 문제라고 느꼈음 😇

3. 5월 식품들의 총매출 조회하기 (LV 4)

정답:

SELECT p.PRODUCT_ID
    ,  p.PRODUCT_NAME
    ,  SUM(p.PRICE * o.AMOUNT) AS TOTAL_SALES
  FROM FOOD_PRODUCT AS p
  JOIN FOOD_ORDER AS o ON p.PRODUCT_ID = o.PRODUCT_ID
 WHERE o.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
 GROUP
    BY p.PRODUCT_ID, p.PRODUCT_NAME
 ORDER
    BY TOTAL_SALES DESC, p.PRODUCT_ID

4. 조건에 맞는 도서와 저자 리스트 출력하기 (LV 2)

정답:

SELECT b.BOOK_ID
    ,  a.AUTHOR_NAME
    ,  DATE_FORMAT(b.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
  FROM BOOK AS b
  JOIN AUTHOR AS a ON b.AUTHOR_ID = a.AUTHOR_ID
 WHERE b.CATEGORY = '경제'
 ORDER
    BY b.PUBLISHED_DATE ASC

5. 그룹별 조건에 맞는 식당 목록 출력하기 (LV 4)

정답:

WITH MEM_REVIEW_CNT AS (
    SELECT m.MEMBER_ID
        ,  m.MEMBER_NAME
        ,  COUNT(REVIEW_ID) AS CNT_REVIEW
      FROM MEMBER_PROFILE AS m
      JOIN REST_REVIEW AS r ON m.MEMBER_ID = r.MEMBER_ID 
     GROUP
        BY m.MEMBER_ID, m.MEMBER_NAME
     ORDER
        BY CNT_REVIEW DESC
)
SELECT c.MEMBER_NAME
    ,  r.REVIEW_TEXT
    ,  DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
  FROM MEM_REVIEW_CNT AS c
  JOIN REST_REVIEW AS r ON c.MEMBER_ID = r.MEMBER_ID 
 WHERE c.CNT_REVIEW = (SELECT MAX(CNT_REVIEW)
                         FROM MEM_REVIEW_CNT)
 ORDER
    BY REVIEW_DATE, r.REVIEW_TEXT

6. 없어진 기록 찾기 (LV 3)

정답:

SELECT o.ANIMAL_ID
    ,  o.NAME
  FROM ANIMAL_INS AS i
RIGHT
  JOIN ANIMAL_OUTS AS o ON i.ANIMAL_ID = o.ANIMAL_ID
 WHERE i.ANIMAL_ID IS NULL
 ORDER
    BY o.ANIMAL_ID