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

[문제]
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. HISTORY 테이블과 CAR 테이블을 CAR_ID로 JOIN 하되 CAR_TYPE이 트럭인 행들만 필터링해서 JOIN 한다

2. DATE_DIFF를 사용하여 대여 기간을 구하고 기간별 할인율을 PLAN 테이블에서 확인해서 가져온다.

3. CASE WHEN 문으로 기간별로 할인율을 배정하는 컬럼을 만들어주고 서브쿼리로 보낸다 (기록ID, 하루요금, 기간, 기간별할인율)

4. 메인쿼리에서 기록ID와 대여금액을 구해준 뒤 출력한다

5. 요금과 ID로 내림차순 정렬한다.


[정답]

SELECT HISTORY_ID,
       ROUND(DAILY_FEE * PERIOD * (1-DISCOUNT_RATE)) AS FEE
FROM
(
    SELECT HISTORY_ID,
           DAILY_FEE,
           DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
           CASE 
               WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 7 THEN 0
               WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 30 THEN 0.05
               WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 90 THEN 0.08
               ELSE 0.15
           END AS DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    JOIN CAR_RENTAL_COMPANY_CAR A
        ON B.CAR_ID = A.CAR_ID
            AND CAR_TYPE = '트럭'
) SUB
ORDER BY FEE DESC, HISTORY_ID DESC