[SQL]특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

프로그래머스

with base as (
select 
    car_id
    , max(if(date('2022-11-01') between start_date and end_date
            or date('2022-11-30') between start_date and end_date, 1, 0)) as con
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by 1
)
, base2 as (
select
    distinct a.car_id
    , a.car_type
    , a.daily_fee
from CAR_RENTAL_COMPANY_CAR a
left join base b on a.car_id = b.car_id
where (a.car_type = '세단' or a.car_type = 'SUV')
    and (b.con = 0 or b.car_id is null)
)

select
    a.car_id
    , a.car_type
    , floor(a.DAILY_FEE*(1-b.DISCOUNT_RATE/100)*30) as FEE
from base2 a
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN b on a.car_type = b.car_type
    and b.DURATION_TYPE = '30일 이상'
where a.DAILY_FEE*(1-b.DISCOUNT_RATE/100)*30 between 500000 and 2000000
order by 3 desc, 2, 1 desc