자동차 대여 기록 별 대여 금액 구하기

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

• 문제 풀이

WITH SAMPLE AS (
                SELECT H.HISTORY_ID, 
                       C.CAR_TYPE,
                       (END_DATE - START_DATE + 1) * C.DAILY_FEE AS "FEE",
                       CASE WHEN END_DATE - START_DATE + 1 >= 90 THEN '90일 이상'
                            WHEN END_DATE - START_DATE + 1 >= 30 THEN '30일 이상'
                            WHEN END_DATE - START_DATE + 1 >= 7 THEN '7일 이상'
                            ELSE '7일 미만'
                       END AS "DURATION_TYPE"
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
                JOIN CAR_RENTAL_COMPANY_CAR C
                ON H.CAR_ID = C.CAR_ID
                WHERE C.CAR_TYPE = '트럭'
               )

SELECT S.HISTORY_ID, FEE * (100 - NVL(P.DISCOUNT_RATE, 0)) / 100 AS "FEE"
FROM SAMPLE S
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON S.CAR_TYPE = P.CAR_TYPE AND S.DURATION_TYPE = P.DURATION_TYPE
ORDER BY FEE DESC, S.HISTORY_ID DESC;

• 실행 결과