[프로그래머스 SQL고득점 키트 JOIN]특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

문제

---

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

프로그래머스

풀이

---

SELECT D.car_id, D.car_type, round((1-P.DISCOUNT_RATE/100)*30 * D.daily_fee) as FEE 
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
JOIN
    (SELECT  C.car_id, C.car_type, C.daily_fee
    FROM CAR_RENTAL_COMPANY_CAR C
    JOIN 
    (select car_id
     FROM CAR_RENTAL_COMPANY_CAR
    where car_id not in(
            (select distinct car_id
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where ('2022-11-01' between start_date and end_date) or ('2022-11-30' between start_date and end_date)) )
    )B
    ON B.car_id=C.car_id
    WHERE CAR_TYPE="SUV" OR CAR_TYPE="세단")D
ON D.car_type=P.car_type
WHERE P.DURATION_TYPE LIKE "30%" 
AND 500000 <= round((1-P.DISCOUNT_RATE/100)*30 * D.daily_fee)  
AND round((1-P.DISCOUNT_RATE/100)*30 * D.daily_fee) < 2000000
order by fee desc, car_type asc, car_id desc;