https://school.programmers.co.kr/learn/courses/30/lessons/157339
select car_id, car_type, fee
from (select b.car_id, a.car_type, (100-a.discount_rate)/ 100 * 30 * DAILY_FEE as fee -- 총 금액이 50이상 200이하인거만 가져오기
from (select car_type, discount_rate -- 30일짜리만 필요하므로 차타입마다 먼저 가져오기
from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
where REGEXP_REPLACE(DURATION_TYPE, '[^0-9]') = 30) a,
CAR_RENTAL_COMPANY_CAR b
where a.car_type = b.car_type
and (100-discount_rate)/ 100 * 30 * DAILY_FEE between 500000 and 1999999
) x
where car_id not in (select car_id -- 11월 1일 부터 11월 30일 사이에 예약된거 빼기
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where to_char(start_date,'yyyy-mm-dd') <= '2022-11-30'
and to_char(end_date,'yyyy-mm-dd') >= '2022-11-01')
order by 3 desc, 2 asc ,1 desc