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

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/151141

 

2. 코드

SQL 1

WITH HISTORY_INFO AS (
    SELECT
        h.HISTORY_ID,
        c.DAILY_FEE,
        c.CAR_TYPE,
        DATEDIFF(h.END_DATE, h.START_DATE) + 1 AS DIFF
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
    JOIN CAR_RENTAL_COMPANY_CAR c
      ON h.CAR_ID = c.CAR_ID
    WHERE c.CAR_TYPE = '트럭'
),
DURATION_INFO AS (
    SELECT
        HISTORY_ID,
        DAILY_FEE,
        CAR_TYPE,
        DIFF,
        CASE
            WHEN DIFF >= 90 THEN '90일 이상'
            WHEN DIFF >= 30 THEN '30일 이상'
            WHEN DIFF >= 7 THEN '7일 이상'
            ELSE NULL
        END AS DURATION_TYPE
    FROM HISTORY_INFO
)
SELECT
    d.HISTORY_ID,
    FLOOR(d.DAILY_FEE * d.DIFF * (100 - IFNULL(p.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM DURATION_INFO d
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
  ON d.CAR_TYPE = p.CAR_TYPE
 AND d.DURATION_TYPE = p.DURATION_TYPE
ORDER BY FEE DESC, d.HISTORY_ID DESC;

 

SQL 2

WITH HISTORY_INFO AS (
    SELECT 
        h.HISTORY_ID,
        c.DAILY_FEE,
        c.CAR_TYPE,
        DATEDIFF(h.END_DATE, h.START_DATE) + 1 AS DIFF,
        CASE 
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE NULL 
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS h
    JOIN CAR_RENTAL_COMPANY_CAR AS c ON h.CAR_ID = c.CAR_ID
    WHERE c.CAR_TYPE = '트럭'
)

SELECT 
    i.HISTORY_ID,
    FLOOR(i.DAILY_FEE * i.DIFF * (100 - IFNULL(p.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM HISTORY_INFO AS i
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS p 
    ON i.DURATION_TYPE = p.DURATION_TYPE 
    AND i.CAR_TYPE = p.CAR_TYPE
ORDER BY FEE DESC, i.HISTORY_ID DESC;

 

3. 메모

• with문, case when에 대해서 햇갈려서 제대로 못 풀음
  • 주의 : 큰 범위 부터 써야 함
• DATEDIFF 함수 → 날짜 차이 계산하는 함수
  • 주의: SELECT에서 정의한 DIFF는 같은 위치의 CASE문에서 인식 불가 → CTE(WITH)를 중첩하거나 식을 재작성
  • 문제에서 종료일을 포함하지 않으므로 +1 이 필요함
• 할인율이 없으면 → NULL이 되어서 ISNULL 처리함