15.프로그래머스 SQL Lv.4 (자동차 대여 기록 별 대여 금액 구하기)

문제 설명
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.

CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

 

문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

문제 파악

1.대여 기간 문제의 기본 : 대여 종료일 - 대여 시작일에 +1 해주기

2.WITH 절 사용 : 트럭의 대여 기록과 대여 일수를 임시 테이블로 정리 해두기

3.조건부 조인 : 대여 일수에 따라 할인 정책 테이블의 DURATION_TYPE 매칭

4.결제 금액 산출 : 일일 대여 요금 * 대여 한 일수 *(1-할인율) 을 계산하고 정수로 출력

 

해결 과정

1. WITH RENTAL_DATA AS ( ... )

: 메인 쿼리를 작성하기 전에 필요한 데이터만 뽑아두는 가상 테이블(CTE) 생성 (코드가 길어지기때문에)

2. DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS DURATION

: 대여 한 기간을 일 단위로 계산. 시작일 당일도 포함해야해서 +1을 무조건 해야한다.

3. WHERE C.CAR_TYPE = '트럭' 

: 문제 조건에 맞게 트럭 데이터만 먼저 보기

4. ROUND(R.DAILY_FEE * R.DURATION * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) AS FEE

: IFNULL(P.DISCOUNT_RATE, 0): 7일 미만의 대여라서 할인 정책 테이블과 매칭되지 않는 데이터(NULL)는 할인율을 0으로 처리, 할인율을 계산해서 일일 요금과 대여 일수를 곱하고 ROUND 함수로 정수 부분만 남기기

5. LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON R.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = CASE ... END

: 조건에 맞춰 텍스트를 매칭하기 위해서LEFT JOIN & CASE WHEN 사용 , 조건에 맞지 않는 7일 미만의 데이터도 살리기 위해 LEFT JOIN 사용

4. ORDER BY FEE DESC, R.HISTORY_ID DESC;

: 계산된 총 대여 금액을 기준으로 내림차순 정렬을 하고 금액이 똑같으면 대여기록 ID 가 큰 것부터 나오도록 2차 정렬

 

 

답변

-- 트럭의 대여 기록과 대여 일수를 먼저 계산하는 임시 테이블 생성(CTE)
WITH RENTAL_DATA AS (
    SELECT 
        H.HISTORY_ID,
        C.CAR_TYPE,
        C.DAILY_FEE,
        DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
    WHERE C.CAR_TYPE = '트럭'
)

-- 계산된 대여 일수를 바탕으로 할인율을 적용하여 최종 요금 산출
SELECT 
    R.HISTORY_ID,
    ROUND(R.DAILY_FEE * R.DURATION * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM RENTAL_DATA R
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
    ON R.CAR_TYPE = P.CAR_TYPE 
    AND P.DURATION_TYPE = CASE 
        WHEN R.DURATION >= 90 THEN '90일 이상'
        WHEN R.DURATION >= 30 THEN '30일 이상'
        WHEN R.DURATION >= 7 THEN '7일 이상'
        ELSE NULL 
    END
ORDER BY FEE DESC, R.HISTORY_ID DESC;

 

 

솔직히 이번 문제는 Lv3 문제를 많이 다뤄 봤다고 생각해서 레벨을 올려서 풀어 보았지만 전에 풀던 문제랑은 차원이 다르게 복잡했다. 처음에는 그냥 3개의 테이블을 join 하고 수식걸면 끝나겠지 했는데 결과 값에서 7일 미만 단기 대여 데이터들이 누락되었다.

원인이 뭘까 했는데 할인 정책이 아예 없는 7일 미만 데이터를 생각 하지 않고 그냥 join 을 걸어버린게 원인 이였다.

전에 풀던 문제에서 배운 동적 조인(LEFT JOIN)을 걸고 ON 조건절 안에 CASE WHEN 을 넣어 대여 일수 구간 별로 매칭 조건을 다르게 주어야 오류없이 답변을 작성할 수 있었다.  이번 문제에는 시간이 굉장히 소모 되었지만 잊고 있었던 문법들을 실전에서 사용해보는 좋은 기회였던 문제였다.