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

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

프로그래머스

 

확실히 레벨 4부터는 난이도 차이가 체감이 확 된다.

 

SELECT
    A.CAR_ID AS 'CAR_ID',
    A.CAR_TYPE AS 'CAR_TYPE',
    ROUND(A.DAILY_FEE*30*((100-C.DISCOUNT_RATE)/100)) AS 'FEE'
    FROM CAR_RENTAL_COMPANY_CAR AS A
        JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B ON A.CAR_ID = B.CAR_ID
        JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C ON A.CAR_TYPE = C.CAR_TYPE
    WHERE
        A.CAR_ID NOT IN
        (
            SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01'
        )
        AND C.DURATION_TYPE LIKE '30%'
GROUP BY A.CAR_ID
HAVING A.CAR_TYPE IN ('SUV', '세단') AND FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;