학습/SQL

[프로그래머스]SQL학습하기 - 자동차 대여 기록 별 대여 금액 구하기

용이03 2025. 2. 27. 14:30

✔️ 문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
✔️ 조건
할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

✔️ LEVEL 4

✔️ 분류 String, Date

 

📌 문제 분석

자동차 대여 기록별 대여 금액을 계산하는 SQL 쿼리를 작성

  • CAR_RENTAL_COMPANY_CAR (자동차 테이블): 자동차의 종류와 일일 대여 요금(daily_fee) 등의 정보를 포함
  • CAR_RENTAL_COMPANY_RENTAL_HISTORY (대여 테이블): 자동차 대여 기록을 관리하며, 대여 기간(start_date, end_date) 및 대여 기록 ID(HISTORY_ID) 등의 정보를 포함
  • CAR_RENTAL_COMPANY_DISCOUNT_PLAN (할인정책 테이블): 자동차 종류별로 대여 기간에 따른 할인율을 제공
요금  =  일일 대여요금 * 총 대여기간 * 할인율

 

자동차 테이블에서 일일 대여요금, 대여테이블에서 대여기간, 할인 정책 테이블에서  할인율 이용하여 요금 계산하기. 

WITH CAR_RENTAL AS (SELECT RH.car_id, HISTORY_ID,
        DATEDIFF(end_date,start_date)+1 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_type,
        car_type,daily_fee
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH LEFT JOIN CAR_RENTAL_COMPANY_CAR C
ON RH.car_id = C.car_id
WHERE car_type = '트럭'
)

SELECT HISTORY_ID, ROUND(IFNULL(daily_fee * DAYS * (100-discount_rate)/100,daily_fee* DAYS)) FEE
FROM CAR_RENTAL A LEFT JOIN (SELECT car_type,duration_type,discount_rate
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE car_type = '트럭') P
ON A.car_type = P.car_type AND A.duration_type = P.duration_type

ORDER BY FEE DESC, HISTORY_ID DESC

 

🚀 풀이 과정

1. WITH 절 활용하여 테이블 조인

먼저, CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 CAR_ID를 기준으로 결합. 하지만 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에는 직접적으로 JOIN할 수 있는 컬럼이 없으므로, 대여 기간 정보를 기준으로 연결할 수 있도록 추가적인 작업이 필요.

 

WITH 절을 활용하여 자동차 테이블과 대여 테이블을 먼저 결합하여 새로운 테이블을 생성.

이때 조건인 자통차 종류가 '트럭', 대여기간 생성, 조건에 맞게 대여기간 4가지 타입으로 나누기 를 실행하였다.

  • WHERE 절로 '트럭' 선택
  • DATEDIFF 함수를 이용하여 대여기간 계산, 대여일과 반납일이 같은 경우에도 최소 1일로 계산하도록 +1 
  • 대여 기간 타입 분류 : CASE WHEN 문을 사용하여 대여 기간을 네 가지 범주로 분류 후 새로운 칼럼 duration_type 생성.
    • 90일 이상: 대여 기간이 90일 이상
    • 30일 이상: 대여 기간이 30일 이상 90일 미만
    • 7일 이상: 대여 기간이 7일 이상 30일 미만
    • 7일 미만: 할인 적용 없음

📍 생성된 테이블은 할인정책 테이블과 결합 가능한 테이블

2. 할인율 적용 및 최종 대여 금액 계산

WITH 로 생성된 테이블과 할인 정책 테이블을  CAR_TYPE과 duration_type을 기준으로 JOIN하여 할인율을  적용.

대여 금액(FEE) 계산 하기

  • 할인율이 존재하는 경우: 1일 대여 금액 * 총 대여일 * (100 - 할인율) / 100
  • 할인율이 없는 경우 (7일 미만 대여): IFNULL을 사용하여 기본 계산(daily_fee * DAYS) 적용

3. 정렬 조건 적용

결과를 대여 금액(FEE) 기준으로 내림차순 정렬, 동일한 금액일 경우 대여 기록 ID(HISTORY_ID) 기준으로 내림차순 정렬

✍️ 풀이 정리

✔ WITH 절 활용 → 서브쿼리 길이를 줄이고 가독성 향상

✔ DATEDIFF 함수 사용 → 대여 기간 계산 (최소 1일 보장)

✔ CASE WHEN 사용 → 대여 기간 타입(duration_type) 분류

✔ LEFT JOIN 활용 → 할인 정책 테이블과 결합

✔ IFNULL 사용 → 할인율이 없는 경우 기본 계산 적용

✔ ORDER BY 적용 → FEE DESC, HISTORY_ID DESC 정렬

 

복잡해 보일 수 있지만, 단계별로 진행하면 논리적으로 해결할 수 있는 문제