[SQL 문제 풀이] 자동차 대여 기록 별 대여 금액 구하기

내가 작성한 쿼리

Oracle

-- 대여일수 및 할인 기간 계산 테이블
WITH HISTORY AS (
    SELECT
        HISTORY_ID,
        CAR_ID,
        (END_DATE - START_DATE + 1) AS DIFF,
        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 NULL
        END AS DURATION_TYPE
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT 
    H.HISTORY_ID,
    FLOOR(
        C.DAILY_FEE 
        * H.DIFF 
        * (1 - NVL(DP.DISCOUNT_RATE, 0) / 100)
    ) AS FEE
FROM 
    HISTORY H
JOIN 
    CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
LEFT JOIN 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP
    ON H.DURATION_TYPE = DP.DURATION_TYPE AND C.CAR_TYPE = DP.CAR_TYPE
WHERE 
    C.CAR_TYPE = '트럭'
ORDER BY 
    FEE DESC,
    H.HISTORY_ID DESC;

MySQL

-- 대여일수 및 할인 기간 계산 테이블
WITH HISTORY AS (
    SELECT
        HISTORY_ID,
        CAR_ID,
        DATEDIFF(END_DATE, START_DATE) + 1 AS DIFF,
        CASE
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE NULL
        END AS DURATION_TYPE
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT 
    H.HISTORY_ID,
    FLOOR(
        C.DAILY_FEE 
        * H.DIFF 
        * (1 - IFNULL(DP.DISCOUNT_RATE, 0) / 100)
    ) AS FEE
FROM 
    HISTORY H
JOIN 
    CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
LEFT JOIN 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP
    ON H.DURATION_TYPE = DP.DURATION_TYPE AND C.CAR_TYPE = DP.CAR_TYPE
WHERE 
    C.CAR_TYPE = '트럭'
ORDER BY 
    FEE DESC,
    H.HISTORY_ID DESC;

차이점

• 날짜 계산 방식의 차이

• NULL처리 함수 차이