[SQL KIT] (2024) 자동차 대여 기록 별 대여 금액 구하기(설명/코드/정답)

 

 

문제 링크 : https://school.programmers.co.kr/learn/courses/30/lessons/151141

 

핵심. 대여기간, 할인율을 구하려고 했는가

아래 코드가 이해가 잘되지 않으면 최하단의 코드를 한 번 더 확인

 

정상 코드.

WITH RENTAL_DATE_AND_DURATION_TYPE AS (
SELECT *, CASE 
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 7 THEN NULL
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 30 THEN '7일 이상'
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 < 90 THEN '30일 이상'
             ELSE '90일 이상' 
           END AS R_DURATION_TYPE
        , DATEDIFF(END_DATE, START_DATE) + 1 AS R_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY JOIN CAR_RENTAL_COMPANY_CAR USING(CAR_ID)
)


SELECT HISTORY_ID, 
       TRUNCATE(R_DATE * DAILY_FEE * (1 - COALESCE(DISCOUNT_RATE, 0)/100), 0) AS FEE
FROM RENTAL_DATE_AND_DURATION_TYPE R LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
     ON R.CAR_TYPE = P.CAR_TYPE AND R.R_DURATION_TYPE = DURATION_TYPE
WHERE R.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

 

 

내부 동작 순서 

 

WITH 절 (

1. FROM 절

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_CAR 테이블을 조인, 조인 조건은 CAR_ID를 기준으로 합니다.

 

2. SELECT 절

R_DURATION_TYPE과 R_DATE를 계산하여 쿼리 결과를 RENTAL_DATE_AND_DURATION_TYPE이라는 임시 테이블에 저장

)

 

1. FROM 절

임시로 저장한 RENTAL_DATE_AND_DURATION_TYPE 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 LEFT 조인합니다.

조인 조건은 CAR_TYPE과 R_DURATION_TYPE입니다.  (그냥 JOIN (== INNER JOIN) 하면 7일 미만인 행들이 사라짐)

 

2. WHERE 절 

CAR_TYPE 이 '트럭'인 행만 필터링

 

3. SELECT 절 

결과로 HISTORY_ID와 계산된 FEE를 선택합니다.

COALESCE 함수를 사용하여 할인율이 NULL인 경우 0으로 대체합니다. TRUNCATE 함수를 사용하여 계산된 요금을 특정 자리수에서 버립니다. 

 

4. ORDER BY 절 

FEE 내림차순으로 정렬, FEE가 같을 경우 HISTORY_ID로 내림차순 정렬

 

 

 

 

 

 

---

 

초기 코드

WITH RENTAL_PERIOD AS (
SELECT *, TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1 AS R_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY JOIN CAR_RENTAL_COMPANY_CAR USING(CAR_ID)
), EXACT_DURATION_TYPE AS(
     SELECT *, CASE 
                 WHEN R_DATE >= 7 AND R_DATE < 30 THEN '7일 이상'
                 WHEN R_DATE >= 30 AND R_DATE < 90 THEN '30일 이상'
                 WHEN R_DATE >= 90 THEN '90일 이상'
                 ELSE '7일 미만' 
               END AS R_DURATION_TYPE
    FROM RENTAL_PERIOD
), EXTRACTING_DURATION_RATE AS (
        SELECT *, MAX(CASE 
                    WHEN R_DURATION_TYPE = '7일 미만' THEN 0
                    WHEN R_DURATION_TYPE =  DURATION_TYPE THEN DISCOUNT_RATE 
                  END ) R_DISCOUNT_RATE 
        FROM EXACT_DURATION_TYPE JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN USING(CAR_TYPE)
        GROUP BY HISTORY_ID
)


SELECT HISTORY_ID, FLOOR(R_DATE * DAILY_FEE * (1 - R_DISCOUNT_RATE / 100)) AS FEE
FROM EXTRACTING_DURATION_RATE
WHERE CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

 

 

=>정상 출력

하지만 쿼리 개선 가능성 있음

WITH RENTAL_PERIOD AS ( //⭐ 대여 기간 계산
SELECT *, TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1 AS RENTAL_DATE 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY JOIN CAR_RENTAL_COMPANY_CAR USING(CAR_ID)
), EXACT_DURATION_TYPE AS( //⭐  계산된 대여 기간을 통해 기간 타입 정하기
     SELECT *, CASE 
                 WHEN RENTAL_DATE < 7 THEN NULL
                 WHEN RENTAL_DATE < 30 THEN '7일 이상'
                 WHEN RENTAL_DATE < 90 THEN '30일 이상'
                 ELSE '90일 이상' 
               END AS R_DURATION_TYPE
    FROM RENTAL_PERIOD
), EXTRACTING_DURATION_RATE AS ( //⭐  정해진 기간 타입으로 할인율 구하기 
        SELECT E.CAR_TYPE, HISTORY_ID, RENTAL_DATE, DAILY_FEE, 
               COALESCE(DISCOUNT_RATE, 0) AS DISCOUNT_RATE 
        FROM EXACT_DURATION_TYPE E LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
             ON E.CAR_TYPE = P.CAR_TYPE AND R_DURATION_TYPE = DURATION_TYPE
        GROUP BY HISTORY_ID
)

 
SELECT HISTORY_ID, FLOOR( RENTAL_DATE * DAILY_FEE * (1 - DISCOUNT_RATE / 100)) AS FEE 
FROM EXTRACTING_DURATION_RATE
WHERE CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC