[SQL]프로그래머스 Lv.4 자동차 대여 기록별 대여 금액 구하기

✅ 코드

WITH RENTAL_HISTORY AS (
   SELECT *
       , CASE
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 7 THEN NULL
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 30 THEN '7일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 90 THEN '30일 이상'
            ELSE '90일 이상'
         END AS DURATION_TYPE
   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
), TRUCKS AS (
    SELECT *
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE = '트럭'
)

SELECT H.HISTORY_ID AS HISTORY_ID
    , ROUND(IF(P.DURATION_TYPE IS NULL,
    		   T.DAILY_FEE * (DATEDIFF(H.END_DATE, H.START_DATE) + 1),
             T.DAILY_FEE * (DATEDIFF(H.END_DATE, H.START_DATE) + 1) * (1 - P.DISCOUNT_RATE/100))) AS FEE
FROM TRUCKS AS T
   INNER JOIN RENTAL_HISTORY AS H ON T.CAR_ID = H.CAR_ID
   LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON T.CAR_TYPE = P.CAR_TYPE
                                           AND H.DURATION_TYPE = P.DURATION_TYPE
GROUP BY HISTORY_ID
ORDER BY FEE DESC
      , HISTORY_ID DESC

WITH문을 사용하여 사전에 2가지 테이블을 생성했습니다.

• TRUCKS: CAR_RENTAL_COMPANY_CAR 테이블에서 '트럭'만 필터링한 테이블
• RENTAL_HISTORY: END_DATE와 START_DATE 컬럼을 사용하여 DURATION_TYPE 컬럼을 새로 생성한 테이블

이후에는 INNER JOIN과 LEFT JOIN을 사용하여 풀이를 진행했습니다.