[SQL 고득점 Kit] String, Date : 자동차 대여 기록 별 대여 금액 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

 

---

 

 

 

최종 쿼리

WITH HISTORY AS(
    SELECT
        A.HISTORY_ID,
        A.CAR_ID,
        B.DAILY_FEE,
        (DATEDIFF(A.END_DATE, A.START_DATE) + 1) AS DURATION,
        CASE
            WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(A.END_DATE, A.START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE '할인 없음'
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
        JOIN CAR_RENTAL_COMPANY_CAR B
        ON A.CAR_ID = B.CAR_ID AND B.CAR_TYPE = '트럭')
        
SELECT
    A.HISTORY_ID,
    CASE
        WHEN A.DURATION_TYPE = '할인 없음' THEN ROUND(A.DAILY_FEE*A.DURATION, 0)
        ELSE ROUND(A.DAILY_FEE*A.DURATION*(1-B.DISCOUNT_RATE/100), 0)
    END AS FEE
FROM HISTORY A
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    ON A.DURATION_TYPE = B.DURATION_TYPE AND B.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;