자동차 대여 기록 별 대여 금액 구하기 | Lv.4

문제

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

 

대여 금액은 '대여 기간'에 따라 차등적으로 계산된다.

1) '대여 기간' = end_date - start_date + 1
2) 자동차 종류가 트럭인 경우, '7일 이상' - 5% 할인율 / '30일 이상' - 7% 할인율 / '90일 이상' - 10% 할인율 적용

 

c - 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 테이블

h - 자동차 대여 기록 정보를 담은 데이블

p - 자동차 종류 및 대여 기간 종류 별 할인 정책 정보를 담은 테이블

 

풀이 

 

우선 자동차 종류가 '트럭'에 해당하는 c 테이블과 h 테이블을 car_id 기준으로 조인함과 동시에, '대여 기간'을 집계한다. 

SELECT h.history_id
     , c.daily_fee
     , c.car_type
     , DATEDIFF(h.end_date, h.start_date) + 1 AS duration
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN CAR_RENTAL_COMPANY_CAR c ON h.car_id = c.car_id AND c.car_type = '트럭'

 

 

위와 같은 결과를 반환하는 테이블을 CTE로 임시 저장하여, 아래의 메인 쿼리를 작성한다.

WITH step1 AS (
    SELECT h.history_id
         , c.daily_fee
         , c.car_type -- ONLY 트럭
         , DATEDIFF(h.end_date, h.start_date) + 1 AS duration
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
        JOIN CAR_RENTAL_COMPANY_CAR c ON h.car_id = c.car_id AND c.car_type = '트럭'

)
SELECT history_id
     , ROUND(CASE 
                WHEN duration < 7 THEN daily_fee * duration
                WHEN duration < 30 THEN daily_fee * (SELECT (1-discount_rate/100) 
                                                                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
                                                                         WHERE p.car_type = '트럭' AND duration_type = '7일 이상') * duration
                WHEN duration < 90 THEN daily_fee * (SELECT (1-discount_rate/100) 
                                                                          FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
                                                                          WHERE p.car_type = '트럭' AND duration_type = '30일 이상') * duration
                ELSE daily_fee * (SELECT (1-discount_rate/100) 
                                    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
                                    WHERE p.car_type = '트럭' AND duration_type = '90일 이상') * duration
             END, 0) AS fee 
FROM step1 s 
ORDER BY fee DESC, history_id DESC

/*
WITH step2 AS (
    SELECT *
         , CASE
              WHEN duration >= 90 THEN (SELECT discount_rate/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE car_type = '트럭' AND duration_type  = '90일 이상') -- 10 -> 0.1
              WHEN duration >= 30 THEN (SELECT discount_rate/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE car_type = '트럭' AND duration_type  = '30일 이상')
              WHEN duration >= 7 THEN (SELECT discount_rate/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE car_type = '트럭' AND duration_type  = '7일 이상')
              ELSE 0
           END AS discount_rate
    FROM (
        SELECT h.history_id
             , c.daily_fee
             , c.car_type 
             , DATEDIFF(h.end_date, h.start_date) + 1 AS duration
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
            JOIN CAR_RENTAL_COMPANY_CAR c ON h.car_id = c.car_id AND c.car_type = '트럭'
    ) step1
)
SELECT history_id
     , ROUND(daily_fee * (1-discount_rate) * duration, 0) AS fee
FROM step2
ORDER BY fee DESC, history_id DESC
*/

대여 기간	할인율
7일 미만	0
7일 이상 30일 미만	5
30일 이상 90일 미만	7
90일 이상	10

 

*대여 금액 = 일일 요금 x (1-할인율/100) x 대여 기간

 

대여 기간에 따라 각각 다른 할인율을 적용해야 하기 때문에 메인 쿼리의 SELECT 절에 상관 서브쿼리를 이용해서 해결했다.

최종 결과 예시