SQL/프로그래머스 - 자동차 대여 기록 별 대여 금액 구하기(CAST, REPLACE, COALESCE)

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

 

진행한 순서

목표: 대여기록(HISTORY_ID) 별로 총 대여금액을 구하는 것. 즉, 1일 요금 * 빌린 날 수 * (1-할인율/100) = 최종 금액.

1. HISTORY_ID 가 기준이므로, CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 기준으로, 빌린 날 수와 DAILY_FEE를 조인해두기
2. 1번 테이블에 조인 할 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 CAR_TYPE 을 트럭으로 필터링해서 만들어두기
3. 1,2번 테이블 조인. 조건은 CAR_TYPE이 아니라, DURATION_TYPE 숫자로. (CAST, REPLACE)
4. 최종적으로 FEE 계산 및 ORDER BY로 정렬.

기억할것:

• IFNULL 대신 COALESCE쓰기. IFNULL은 MYSQL의 함수라고 한다(!)
• 조건을   ON DC.RENT_DAYS >= TD.DURATION_INT  이렇게 조인했더라도 LEFT JOIN + COALESCE라면 문제 해결

답
-- 운행한 일자를 먼저 구하기
WITH DAY_COUNT AS (
    SELECT H.HISTORY_ID
            , H.CAR_ID
            , DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS RENT_DAYS
            , C.CAR_TYPE
            , C.DAILY_FEE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        JOIN CAR_RENTAL_COMPANY_CAR C
        ON H.CAR_ID = C.CAR_ID
    WHERE C.CAR_TYPE = '트럭'
),
-- DISCOUNT_PLAN에서 TRUCK만 빼놓기
TRUCK_DISCOUNT AS (
    SELECT CAR_TYPE, CAST(REPLACE(DURATION_TYPE, '일 이상','') AS UNSIGNED) AS DURATION_INT, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
),
DC_RATE AS (
    SELECT DC.HISTORY_ID
            , DC.RENT_DAYS
            , DC.DAILY_FEE
            , COALESCE(MAX(TD.DISCOUNT_RATE) OVER (PARTITION BY DC.HISTORY_ID),0) AS DISCOUNT_RATE
    FROM DAY_COUNT DC
        LEFT JOIN TRUCK_DISCOUNT TD
        ON DC.RENT_DAYS >= TD.DURATION_INT
    GROUP BY DC.HISTORY_ID
)
SELECT HISTORY_ID
        , ROUND(RENT_DAYS * DAILY_FEE * (1-(DISCOUNT_RATE/100))) AS FEE
FROM DC_RATE
ORDER BY FEE DESC, HISTORY_ID DESC;