[SQL] 자동차 대여 기록 별 금액 구하기

문제 유형

• SQL
• String, Date

 

문제 난이도

• LEV4

 

문제 분석

문제에서 주어진 조건은 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 문제이다.

구하고자 하는 칼럼: 대역 기록에 대하여 대여 기록 별로 대여 금액(FEE)
정렬 조건: FEE DESC, HISTORY_ID DESC

우선 문제에서 KEY Point는
1. 대여 기간에 따른 할인율을 적용시켜 대여 금액을 구할 수 있는가? (DATEDIFF, CASE WHEN 절 활용)
2. JOIN을 적절하게 활용할 수 있는가?

 

코드 분석

 

CTE 절부터 확인해보자. 우선 CAR_RENTAL_COMPANY_CAR 와 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 활용해서 대여 기간을 구하고 대여 기간별 타입에 맞게 칼럼을 추가해주었다.

 

DATEDIFF(A, B) = A - B 날짜간의 차이를 구하는 것이다.

DATEDIFF(END_DATE + 1, START_DATE) -> 안에는 날짜 타입을 넣어줘야 하므로 다음과 같이 하면 계산이 제대로 동작하지 않는다.

 

CASE WHEN 절은 다음과 같이 활용하고, 마지막에는 THEN을 붙이지 않고, END AS 를 활용해서 해당 칼럼의 별칭(alias)을 정할 수 있다.

WITH RENT AS (
    SELECT
        HISTORY_ID,
        A.CAR_ID,
        B.CAR_TYPE,
        B.DAILY_FEE,
        DATEDIFF(END_DATE, START_DATE) + 1 AS DAYS,
    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 '7일 미만' END AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
    JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID
    WHERE B.CAR_TYPE = '트럭'

)

 

가상 테이블을 활용해서 Join하여 다음과 같이 진행하였다. 우선 할인을 받든 안받든 모든 대여 기록에 대해 대여 금액을 나타내줘야하기 때문에 다음과 같이 DISCOUNT_RATE에 대해서 IFNULL을 활용했다.

 

여기서 ON 절에 DURATION_TYPE을 비교해줬다.

 만약, WHERE절에 조건을 추가한다면 할인 비율이 없는 경우에는 칼럼에 누락되므로, LEFT JOIN과 ON 절에 조건을 추가하여, DISCOUNT_RATE = NULL인 부분도 함께 추가하게 되었다.

SELECT
    A.HISTORY_ID,
    FLOOR(((100 -  IFNULL(B.DISCOUNT_RATE, 0)) / 100) * A.DAILY_FEE * A.DAYS) AS FEE
FROM RENT A
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B 
ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION = B.DURATION_TYPE
ORDER BY FEE DESC, A.HISTORY_ID DESC

전체 코드

WITH RENT AS (
    SELECT
        HISTORY_ID,
        A.CAR_ID,
        B.CAR_TYPE,
        B.DAILY_FEE,
        DATEDIFF(END_DATE, START_DATE) + 1 AS DAYS,
    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 '7일 미만' END AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
    JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID
    WHERE B.CAR_TYPE = '트럭'

)

SELECT
    A.HISTORY_ID,
    FLOOR(((100 -  IFNULL(B.DISCOUNT_RATE, 0)) / 100) * A.DAILY_FEE * A.DAYS) AS FEE
FROM RENT A
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B 
ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION = B.DURATION_TYPE
ORDER BY FEE DESC, A.HISTORY_ID DESC