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

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

프로그래머스

with DISCOUNT_PLAN as (
    select 
        CAR_TYPE
        , REGEXP_SUBSTR(DURATION_TYPE, '[0-9]+') as 'duration' 
        , DISCOUNT_RATE
    from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
)
, RENTAL_HISTORY as (
select 
    HISTORY_ID, CAR_ID
    , datediff(END_DATE, START_DATE) + 1 as 'duration'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
)
, total_rental as (
select
    b.history_id
    , max(if(c.discount_rate is null, 0, c.discount_rate))as discount_rate
From CAR_RENTAL_COMPANY_CAR as a
join RENTAL_HISTORY as b on a.CAR_ID = b.CAR_ID
left join DISCOUNT_PLAN as c on a.CAR_TYPE = c.CAR_TYPE
    and b.duration >= c.duration
group by 1
)
SELECT 
    distinct b.HISTORY_ID
    , floor(a.DAILY_FEE*(1-c.DISCOUNT_RATE/100)*b.duration) as FEE
From CAR_RENTAL_COMPANY_CAR as a
join RENTAL_HISTORY as b on a.CAR_ID = b.CAR_ID
join total_rental as c on b.history_id = c.history_id
where a.CAR_TYPE = '트럭'
order by 2 desc, 1 desc