13.sql

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

SELECT
    HISTORY_ID,
    ROUND(DAILY_FEE * IFNULL(DISCOUNTED_PRICE, 1) * RENTAL_DAYS, 0) AS FEE
FROM (
    SELECT
        HISTORY_ID,
        CAR_TYPE,
        DAILY_FEE,
        DATEDIFF(END_DATE, START_DATE) + 1 AS RENTAL_DAYS,
        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 NULL
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY
    USING (CAR_ID)
    WHERE CAR_TYPE = '트럭'
) AS TRUCK
LEFT JOIN (
    SELECT
        CAR_TYPE,
        DURATION_TYPE,
        (100 - DISCOUNT_RATE) / 100 AS DISCOUNTED_PRICE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
) AS TRUCK_DISCOUNT_INFORMATION
USING (CAR_TYPE, DURATION_TYPE)
ORDER BY FEE DESC, HISTORY_ID DESC;