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

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

 

CASE WHEN을 사용해서 DURATION_TYPE이 아닌 실제 빌린 일 수를 기준으로 할인율을 적용해야 하는게 핵심 포인트다. 

오답 코드

WITH CAR_RENTAL AS (
SELECT
    C.CAR_TYPE,
    C.DAILY_FEE,
    H.HISTORY_ID,
    DATEDIFF(H.END_DATE, H.START_DATE) +1 AS DATE_DIFF
FROM 
    CAR_RENTAL_COMPANY_CAR AS C
INNER JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
ON
    C.CAR_ID = H.CAR_ID
INNER JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
ON 
    H.CAR_TYPE = P.CAR_TYPE)

SELECT
    HISTORY_ID,
    CASE
        WHEN DATE_DIFF BETWEEN 7 AND 29 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상'))
        
        WHEN DATE_DIFF BETWEEN 30 AND 89 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상'))
        
        WHEN DATE_DIFF >= 90 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상'))
        ELSE  ROUND(DAILY_FEE * DATE_DIFF)
    END AS FEE
FROM
    CAR_RENTAL
WHERE
    CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

WITH로 만든 CTE에 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 데이터 셋을 결합하게 되면 HISTORY_ID 별로 여러 할인율의 행이 생성된다.

예를 들어 HISTORY_ID가 111인 차량이 여러 번 대여되어 5%, 8%, 10%의 할인율 기록이 있을 때 CTE에 하나의 HISTORY_ID 행이 총 3개의 행으로 복제 된다.

데이터 행 복제 예시

이렇게 되면 CASE WHEN에서 SUBQUERY를 사용해서 할인율 정보를 가져올 때 하나의 HISTORY_ID에 있는 할인율 정보가 아닌 여러 HISTORY_ID에 있는 할인율 정보(예: 7%)를 가져오게 된다.

이 경우, Subquery returns more than 1 row 에러가 발생한다. Subquery를 사용해서 행별 값을 하나씩 계산해야 되는데 여러 HISTORY_ID가 반환되면 동시에 연산을 할 수 없는 Subquery는 에러가 발생한다.

에러를 방지하기 위해 CTE에서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 제외하고, Subquery에서 직접 참조할 수 있게 변경만 하면 된다.

정답 코드

WITH CAR_RENTAL AS (
SELECT
    C.CAR_TYPE,
    C.DAILY_FEE,
    H.HISTORY_ID,
    DATEDIFF(H.END_DATE, H.START_DATE) +1 AS DATE_DIFF
FROM 
    CAR_RENTAL_COMPANY_CAR AS C
INNER JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
ON
    C.CAR_ID = H.CAR_ID)

SELECT
    HISTORY_ID,
    CASE
        WHEN DATE_DIFF BETWEEN 7 AND 29 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상'))
        
        WHEN DATE_DIFF BETWEEN 30 AND 89 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상'))
        
        WHEN DATE_DIFF >= 90 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상'))
        ELSE  ROUND(DAILY_FEE * DATE_DIFF)
    END AS FEE
FROM
    CAR_RENTAL
WHERE
    CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

 

WITH CTE에서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 없애고 Subquery에 직접 참조하게 추가하면 끝!

 

데이터를 결합할 때 어떤 형식으로 결합되는지, 문제에 적합하게 결합되는지를 확인해야 한다.