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

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

프로그래머스

-- 코드를 입력하세요
# 11월에 렌탈 가능한 세단과 SUV 차량 목록 
WITH TEMP AS(
SELECT C.CAR_ID,
        C.CAR_TYPE,
        DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C 
    LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID= H.CAR_ID 
WHERE (C.CAR_TYPE ='세단' OR C.CAR_TYPE = 'SUV')
AND C.CAR_ID NOT IN (SELECT CAR_ID
                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE DATE_FORMAT(END_DATE, '%Y-%m') >= '2022-11'
                    AND DATE_FORMAT(START_DATE, '%Y-%m') <= '2022-12')
)

SELECT T.CAR_ID AS CAR_ID,
        T.CAR_TYPE AS CAR_TYPE,
        ROUND((DAILY_FEE*(100-DISCOUNT_RATE)/100)*30) AS FEE 
FROM TEMP T
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON T.CAR_TYPE = D.CAR_TYPE 
                                            AND D.DURATION_TYPE = '30일 이상'
GROUP BY 1,2
HAVING FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC