[String, Date 1~6] SQL 코딩테스트 고득점 Kit

1. 자동차 평균 대여 기간 구하기 (LV 2)
2. 특정 옵션이 포함된 자동차 리스트 구하기 (LV 1)
3. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 (LV 3)
4. 자동차 대여 기록 별 대여 금액 구하기 (LV 4)
5. 조건별로 분류하여 주문상태 출력하기 (LV 3)
6. 대여 기록이 존재하는 자동차 리스트 구하기 (LV 3)

1. 자동차 평균 대여 기간 구하기 (LV 2)

정답:

SELECT CAR_ID
    ,  ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
 GROUP
    BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE) + 1) >= 7
 ORDER
    BY AVERAGE_DURATION DESC, CAR_ID DESC

와 계속 오답만 떠서 도대체 뭐가 문제인가 했는데
DATEDIFF(END_DATE, START_DATE) + 1 까지 해야 대여일이 되는 거였다.. 😲

2. 특정 옵션이 포함된 자동차 리스트 구하기 (LV 1)

정답:

SELECT *
  FROM CAR_RENTAL_COMPANY_CAR
 WHERE OPTIONS LIKE '%네비게이션%'
 ORDER
    BY CAR_ID DESC

3. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 (LV 3)

정답:

SELECT CONCAT('/home/grep/src/', b.BOARD_ID, '/', f.FILE_ID, f.FILE_NAME, f.FILE_EXT) AS FILE_PATH
  FROM USED_GOODS_BOARD AS b
  JOIN USED_GOODS_FILE AS f ON b.BOARD_ID = f.BOARD_ID
 WHERE b.VIEWS = (SELECT MAX(VIEWS)
                    FROM USED_GOODS_BOARD)
 ORDER
    BY f.FILE_ID DESC

4. 자동차 대여 기록 별 대여 금액 구하기 (LV 4)

정답:

WITH RENT_INFO AS (
    SELECT h.HISTORY_ID
        ,  c.CAR_ID
        ,  c.DAILY_FEE
        ,  c.CAR_TYPE
        ,  DATEDIFF(h.END_DATE, h.START_DATE) + 1 AS RENTAL_DAYS
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS h
      JOIN CAR_RENTAL_COMPANY_CAR AS c ON h.CAR_ID = c.CAR_ID
     WHERE c.CAR_TYPE = '트럭'
)
, DISCOUNTED AS (
    SELECT r.HISTORY_ID
        ,  r.CAR_ID
        ,  r.CAR_TYPE
        ,  r.DAILY_FEE
        ,  r.RENTAL_DAYS
        ,  COALESCE(MAX(p.DISCOUNT_RATE), 0) AS DISCOUNT_RATE
      FROM RENT_INFO AS r
    LEFT
      JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS p ON r.CAR_TYPE = p.CAR_TYPE
       AND ((p.DURATION_TYPE = '90일 이상' AND r.RENTAL_DAYS >= 90) OR
           (p.DURATION_TYPE = '30일 이상' AND r.RENTAL_DAYS >= 30) OR
           (p.DURATION_TYPE = '7일 이상' AND r.RENTAL_DAYS >= 7))
     GROUP
        BY r.HISTORY_ID, r.CAR_ID, r.CAR_TYPE, r.DAILY_FEE, r.RENTAL_DAYS
)
SELECT HISTORY_ID
    ,  ROUND(DAILY_FEE * RENTAL_DAYS * (1 - DISCOUNT_RATE/100), 0) AS FEE
  FROM DISCOUNTED
 ORDER 
    BY FEE DESC, HISTORY_ID DESC

쿼리 설명:

• COALESCE(MAX(p.DISCOUNT_RATE), 0) 조건 필요
  → 대여 기간이 7일 미만인 경우 할인정책이 없어서 p.DISCOUNT_RATE 값이 NULL 이 되는데, 그러면 FEE 계산식이 오류남
• JOIN 조건으로 DURATION_TYPE 이랑 RENTAL_DAYS 같이 묶는 이유는
  → 할인 조건 중 해당되는 거만 JOIN 후 GROUP BY + MAX()로 가장 높은 할인율 하나만 뽑는 것

예를 들어서..
대여일수가 35일이라면,
DURATION_TYPE AND RENTAL_DAYS 조건에 의해
< DISCOUNT_PLAN >
'7일 이상' → 5%
'30일 이상' → 7%
'90일 이상' → X
⟶ 세 조건 중 '7일 이상'과 '30일 이상'만 JOIN되고, 이후
COALESCE(MAX(DISCOUNT_RATE), 0) 에 의해
→ MAX(5%, 7%) = 7% 로 할인율이 적용되는 것임

이게 왜 LV 5가 아닌건데 도대체

5. 조건별로 분류하여 주문상태 출력하기 (LV 3)

정답:

SELECT ORDER_ID
    ,  PRODUCT_ID
    ,  DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE
    ,  CASE WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
            WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
            WHEN OUT_DATE IS NULL THEN '출고미정'
       END AS '출고여부'
  FROM FOOD_ORDER
 ORDER
    BY ORDER_ID

6. 대여 기록이 존재하는 자동차 리스트 구하기 (LV 3)

정답:

SELECT DISTINCT c.CAR_ID
  FROM CAR_RENTAL_COMPANY_CAR AS c
  JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS h ON c.CAR_ID = h.CAR_ID
 WHERE c.CAR_TYPE = '세단'
   AND MONTH(h.START_DATE) = 10
 ORDER
    BY c.CAR_ID DESC