자동차 대여 기록 별 대여 금액 구하기

문제

코드

WITH DURATION AS(SELECT HISTORY_ID, CAR_ID, (DATEDIFF(END_DATE, START_DATE)+1)AS DIFFDAY, 
                 (CASE
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90 THEN '90일 이상'
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '30일 이상'
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 THEN '7일 이상'
                    ELSE '할인 없음' END) AS DURATION_TYPE
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY), DISCOUNT AS (SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
                                                                        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                                                                      WHERE CAR_TYPE = '트럭')
SELECT D.HISTORY_ID, (CASE
                        WHEN D.DURATION_TYPE = '할인 없음' THEN FLOOR(C.DAILY_FEE * DIFFDAY)
                        ELSE FLOOR((1-(0.01*DIS.DISCOUNT_RATE)) * (C.DAILY_FEE * DIFFDAY))
                     END) AS FEE
                     
FROM DURATION D LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON D.CAR_ID = C.CAR_ID
                LEFT JOIN DISCOUNT DIS ON DIS.DURATION_TYPE = D.DURATION_TYPE
                
WHERE C.CAR_TYPE = '트럭'

ORDER BY FEE DESC, D.HISTORY_ID DESC

 

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/151141