[프로그래머스] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

-- 11월에 대여가능한 세단, SUV
WITH AVAILABLE_CAR AS(
SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C
WHERE C.CAR_TYPE IN ('세단', 'SUV')
    AND NOT EXISTS(
    SELECT 1
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        WHERE H.CAR_ID = C.CAR_ID
            AND H.START_DATE <= '2022-11-30' 
            AND H.END_DATE >= '2022-11-01'
    )
),
-- 30일 이상 할인율
CAR_DISCOUNT AS(
    SELECT A.CAR_ID, A.CAR_TYPE, A.DAILY_FEE, D.DISCOUNT_RATE
    FROM AVAILABLE_CAR A
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
    ON A.CAR_TYPE = D.CAR_TYPE
    AND D.DURATION_TYPE = '30일 이상'
),
-- 요금 계산
CAR_FEE AS(
SELECT 
    CAR_ID,
    CAR_TYPE,
    FLOOR(DAILY_FEE * 30*((100-DISCOUNT_RATE)/100)) FEE
    FROM CAR_DISCOUNT
)
    
SELECT 
    CAR_ID,
    CAR_TYPE,
    FEE
    FROM CAR_FEE
    WHERE FEE >= 500000
    AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;