[프로그래머스SQL]자동차 대여 기록별 대여금액 구하기

문제

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

• oracle 기준

-- sol.1

select HISTORY_ID, (1-FINAL_DISCOUNT_RATE)*DIFF*DAILY_FEE as FEE
from (
SELECT DISTINCT
    F.CAR_ID,
H.HISTORY_ID,
H.END_DATE-H.START_DATE+1 AS DIFF
,CASE WHEN H.END_DATE-H.START_DATE+1 BETWEEN 7 AND 29 THEN 0.05
WHEN H.END_DATE-H.START_DATE+1 BETWEEN 30 AND 89 THEN 0.08
WHEN H.END_DATE-H.START_DATE+1>=90 THEN 0.15
ELSE 0 END FINAL_DISCOUNT_RATE 
,DAILY_FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
LEFT JOIN CAR_RENTAL_COMPANY_CAR F ON H.CAR_ID=F.CAR_ID
--JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON P.CAR_ID=H.CAR_ID
WHERE F.CAR_TYPE='트럭'
    ) 
ORDER BY FEE DESC, HISTORY_ID DESC

이 방법은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN테이블을 쿼리에 활용하지 않고 DISCOUNT_RATE를 하드코딩한 방식이라 유지보수성 측면에서 활용성이 떨어진다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY을 FROM절에 위치하는 메인테이블로 놓을 때
JOIN CAR_RENTAL_COMPANY_CAR는 CAR_ID가 JOIN의 키가 되지만
CAR_RENTAL_COMPANY_DISCOUNT_PLAN테이블의 경우는 바로 조인을 할 수 없다.
조인을 하기 위해서는 CAR_TYPE, DURATION_TYPE 컬럼을 키로 활용하고 DISCOUNT_RATE컬럼을 계산식에 활용해야 할 것이다.

이때 CAR_RENTAL_COMPANY_RENTAL_HISTORY을 테이블의 START_DATE와 END_DATE컬럼을 활용해 DURATION_TYPE 컬럼의 규칙이 적용된다.

이를 활용해 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 DURATION_TYPE컬럼을 생성한 뒤 조인을 해주면 문제가 해결된다.

-- sol.2
SELECT HISTORY_ID, 
		DAILY_FEE * DURATION * (100-NVL(DISCOUNT_RATE, 0))/100 AS FEE,
		DISCOUNT_RATE
FROM
    (
        SELECT 
            HISTORY_ID, CAR_TYPE, DAILY_FEE, END_DATE - START_DATE + 1 AS DURATION,
            CASE WHEN END_DATE - START_DATE >= 90 THEN 90
                    WHEN END_DATE - START_DATE >= 30 THEN 30
                    WHEN END_DATE - START_DATE >= 7 THEN 7
                    ELSE 0
                END || '일 이상' AS DURATION_TYPE
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY HISTORY
            LEFT JOIN CAR_RENTAL_COMPANY_CAR CAR
            USING (CAR_ID)
            WHERE CAR_TYPE = '트럭'
    ) CAR
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DISCOUNT
    USING (CAR_TYPE, DURATION_TYPE)
ORDER BY FEE DESC, HISTORY_ID DESC