I am an amazing person.

1 분 소요

문제 링크

정답

-- CAR_RENTAL_COMPANY_CAR: CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
-- CAR_RENTAL_COMPANY_RENTAL_HISTORY: HISTORY_ID, CAR_ID, START_DATE, END_DATE
-- CAR_RENTAL_COMPANY_DISCOUNT_PLAN: PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE

-- CAR_TYPE = 트럭
-- RENTAL_HISTORY에서 HISTORY_ID 별로 
    -- 금액을 구하여 (DAILY_FEE) * (DURATION) * (DISCOUNT_RATE)

-- 대여 기록 중 트럭의 대여 기간 구하기
WITH TRUCK_CAR_ID AS (
    SELECT
        CAR_ID, DAILY_FEE
    FROM
        CAR_RENTAL_COMPANY_CAR
    WHERE 
        CAR_TYPE = '트럭'
),
TRUCK_RENTAL_DURATION AS (
    SELECT 
        T1.HISTORY_ID,
        T2.DAILY_FEE,
        DATEDIFF(T1.END_DATE, T1.START_DATE) + 1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS T1
    JOIN TRUCK_CAR_ID AS T2
        ON T1.CAR_ID = T2.CAR_ID
),
TRUCK_DISCOUNT_PLAN AS (
    SELECT DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
)

SELECT 
    T1.HISTORY_ID,
    -- T1.DURATION, T2.DURATION_TYPE, T2.DISCOUNT_RATE,
    ROUND(T1.DAILY_FEE * T1.DURATION * (1 - IFNULL(T2.DISCOUNT_RATE, 0) * 0.01)) AS FEE
FROM TRUCK_RENTAL_DURATION AS T1
LEFT JOIN TRUCK_DISCOUNT_PLAN AS T2
ON
    (T1.DURATION >= 90 AND T2.DURATION_TYPE = '90일 이상')
    OR (T1.DURATION >= 30 AND T1.DURATION < 90 AND T2.DURATION_TYPE = '30일 이상')
    OR (T1.DURATION >= 7 AND T1.DURATION < 30 AND T2.DURATION_TYPE = '7일 이상')
ORDER BY 2 DESC, 1 DESC;

✅ Point

  • 내 기준 programmers sql 중 가장 복잡했던 문제
  • 구하고자 하는 값: 전체 대여 기록 중 트럭의 기록 별 대여 금액 (대여 기간 별 차등 할인 적용)
  • table은 총 3개. 각 table에서 반드시 필요한 정보는 다음과 같음
    • CAR_RENTAL_COMPANY_CAR: CAR_TYPE, DAILY_FEE => 트럭의 일일 대여 요금
    • CAR_RENTAL_COMPANY_RENTAL_HISTORY: START_DATE, END_DATE => 대여 기간
    • CAR_RENTAL_COMPANY_DISCOUNT_PLAN: CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE => 트럭의 기간 별 할인율

참고