SQL 코드카타: 자동차 대여 기록 별 대여 금액 구하기

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

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과  CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요.

결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

---

1. TRUCK_LIST 테이블 만들기: '트럭'인 자동차의 대여기록에 대한 테이블

WITH TRUCK_LIST AS (
    SELECT 
           HS.HISTORY_ID,
           CAR.CAR_TYPE,
           -- 날짜 차이 구하기
           DATEDIFF(HS.END_DATE, HS.START_DATE) + 1 AS RENTAL_DAYS,
           CAR.DAILY_FEE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HS
    JOIN CAR_RENTAL_COMPANY_CAR AS CAR
        ON CAR.CAR_ID = HS.CAR_ID
    WHERE CAR.CAR_TYPE = '트럭'
)

2. FEE_PLAN 테이블 만들기: 대여기록 별 대여금액에 대한 테이블

FEE_PLAN AS (
    SELECT 
    	  -- 문자 제거
          REPLACE(DURATION_TYPE, '일 이상', '') AS MIN_DAYS,
          REPLACE(DISCOUNT_RATE, '%', '') AS DISCOUNT_RATE,
          CAR_TYPE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
)

 

3. 메인 SELECT문

SELECT HISTORY_ID,
       MIN(RENTAL_DAYS * DAILY_FEE * (100 - IFNULL(DISCOUNT_RATE, 0)) / 100) AS FEE
FROM TRUCK_LIST TL
LEFT OUTER JOIN FEE_PLAN 
    ON TL.CAR_TYPE = FEE_PLAN.CAR_TYPE 
    AND RENTAL_DAYS >= MIN_DAYS
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC

 

• LEFT OUTER JOIN:
  • TRUCK_LIST와 FEE_PLAN을 연결하여 할인율 가져오기
  • RENTAL_DAYS >= MIN_DAYS: 대여 일수가 최소 일수를 충족해야 해당 할인율 적용
• RENTAL_DAYS * DAILY_FEE * (100 - IFNULL(DISCOUNT_RATE, 0)) / 100:
  • 대여 일수(RENTAL_DAYS)와 하루 대여 요금(DAILY_FEE)을 곱하여 기본 금액 계산
  • 할인율이 적용되면 (100 - DISCOUNT_RATE)로 계산
  • 할인율이 없으면(DISCOUNT_RATE가 NULL), 0% 할인으로 처리
• MIN 함수:
  • 대여 금액이 여러 할인율 조건에 해당할 경우 가장 낮은 금액 선택
• GROUP BY HISTORY_ID:
  • 대여 기록별로 결과 집계
• ORDER BY FEE DESC, HISTORY_ID DESC:
  • 대여 금액을 기준으로 내림차순 정렬
  • 금액이 같으면 대여 기록 ID를 기준으로 내림차순 정렬

 

---

4. 최종 SQL문

WITH TRUCK_LIST AS (
    SELECT 
           HS.HISTORY_ID
         , CAR.CAR_TYPE
         , DATEDIFF(HS.END_DATE, HS.START_DATE) + 1 AS RENTAL_DAYS
         , CAR.DAILY_FEE 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HS
    INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR
        ON CAR.CAR_ID = HS.CAR_ID
    WHERE CAR.CAR_TYPE = '트럭'
),
FEE_PLAN AS (
    SELECT 
          REPLACE(DURATION_TYPE, '일 이상', '') AS MIN_DAYS
        , REPLACE(DISCOUNT_RATE, '%', '') AS DISCOUNT_RATE
        , CAR_TYPE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
)
SELECT HISTORY_ID
     , MIN(RENTAL_DAYS * DAILY_FEE * (100-IFNULL(DISCOUNT_RATE,0)) / 100 ) AS FEE
FROM TRUCK_LIST TL
LEFT OUTER JOIN FEE_PLAN 
    ON TL.CAR_TYPE = FEE_PLAN.CAR_TYPE 
    AND RENTAL_DAYS >= MIN_DAYS 
GROUP BY HISTORY_ID
ORDER BY FEE DESC , HISTORY_ID DESC