[Programmers/Oracle] Level 4 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

✔️ Question

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

프로그래머스

 

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

👨‍💻 Solution

SELECT A.CAR_ID, A.CAR_TYPE, (A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN (SELECT CAR_TYPE, DISCOUNT_RATE
     FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
     WHERE CAR_TYPE IN ('SUV', '세단')
     AND DURATION_TYPE = '30일 이상') B
ON A.CAR_TYPE = B.CAR_TYPE
WHERE CAR_ID NOT IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE END_DATE >= TO_DATE('20221101', 'YYYYMMDD')
                AND START_DATE < TO_DATE('20221201', 'YYYYMMDD'))
AND (A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) >= 500000
AND (A.DAILY_FEE * 30 * (100 - B.DISCOUNT_RATE) / 100) < 2000000
ORDER BY 3 DESC, 2, 1 DESC;