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

문제

코드

WITH CAR AS (SELECT CAR_ID
             FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
             GROUP BY CAR_ID
             HAVING MAX(END_DATE) < '2022-11-01' AND MAX(START_DATE) < '2022-11-01'), 
             DISCOUNT AS (SELECT CAR_TYPE, DISCOUNT_RATE
                          FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                          WHERE DURATION_TYPE = '30일 이상')
SELECT CAR.CAR_ID, C.CAR_TYPE, (CASE 
                            WHEN C.CAR_TYPE = 'SUV' THEN FLOOR((C.DAILY_FEE*30)*(1- 0.01* DISCOUNT_RATE))
                            WHEN C.CAR_TYPE = '세단' THEN FLOOR((C.DAILY_FEE*30)*(1- 0.01* DISCOUNT_RATE))
                            END ) AS FEE
FROM CAR LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON CAR.CAR_ID = C.CAR_ID
         LEFT JOIN DISCOUNT D ON C.CAR_TYPE = D.CAR_TYPE
WHERE (C.CAR_TYPE = '세단' OR C.CAR_TYPE = 'SUV') 
  AND (CASE 
        WHEN C.CAR_TYPE = 'SUV' THEN (C.DAILY_FEE * 30) * (1 - 0.01 * COALESCE(DISCOUNT_RATE, 0))
        WHEN C.CAR_TYPE = '세단' THEN (C.DAILY_FEE * 30) * (1 - 0.01 * COALESCE(DISCOUNT_RATE, 0))
     END) BETWEEN 500000 AND 1999999

 

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/157339