[프로그래머스]SQL학습 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

✔️ 문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

✔️ LEVEL 4

✔️ 분류 JOIN

 

📌 문제 분석

✔️ 주어진 테이블

1. CAR_RENTAL_COMPANY_CAR (자동차 테이블)
   • 자동차 ID (CAR_ID), 자동차 종류 (CAR_TYPE), 1일 대여 요금 (DAILY_FEE) 등의 정보를 포함.
2. CAR_RENTAL_COMPANY_RENTAL_HISTORY (대여 이력 테이블)
   • 자동차 ID (CAR_ID), 대여 시작일 (START_DATE), 대여 종료일 (END_DATE) 등의 정보를 포함.
3. CAR_RENTAL_COMPANY_DISCOUNT_PLAN (할인 정책 테이블)
   • 자동차 종류 (CAR_TYPE), 대여 기간 유형 (DURATION_TYPE), 할인율 (DISCOUNT_RATE) 정보를 포함.

✔️ 문제 요구 사항

• 자동차 종류가 '세단' 또는 'SUV'인 자동차 중에서,
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고,
• 30일간의 대여 금액이 50만 원 이상, 200만 원 미만인 자동차를 찾기.
• 출력 컬럼: 자동차 ID, 자동차 종류, 최종 대여 금액(FEE).
• 정렬 조건:
  1. 대여 금액(FEE) 내림차순
  2. 자동차 종류(CAR_TYPE) 오름차순
  3. 자동차 ID(CAR_ID) 내림차순

정답 제출 코드

WITH RANTAL_CAR AS (
    SELECT C.car_id,car_type,daily_fee, daily_fee * 30 FEE1,
        CASE WHEN start_date <= '2022-11-30' AND end_date >= '2022-11-01' 
                THEN '대여불가' 
        ELSE '대여가능' END AS YN
FROM CAR_RENTAL_COMPANY_CAR C RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
WHERE CAR_TYPE IN ('세단','SUV')
)

SELECT DISTINCT  R.car_id, R.CAR_TYPE, 
                 ROUND(FEE1 *(100 - DISCOUNT_RATE )/100) FEE
FROM RANTAL_CAR R LEFT JOIN (SELECT *
                             FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
                             WHERE duration_type = '30일 이상') P
ON R.CAR_TYPE = P.CAR_TYPE
WHERE CAR_ID NOT IN (SELECT CAR_ID FROM RANTAL_CAR WHERE YN = '대여불가')
      AND FEE1 *(100 - DISCOUNT_RATE )/100 BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

✍ 코드 해석

🔹 WITH 절 (임시 테이블 생성)

WITH RANTAL_CAR AS (
    SELECT C.car_id, car_type, daily_fee, daily_fee * 30 FEE1,
        CASE
            WHEN start_date <= '2022-11-30' AND end_date >= '2022-11-01'
                THEN '대여불가'
            ELSE '대여가능'
        END AS YN
    FROM CAR_RENTAL_COMPANY_CAR C
    RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
    WHERE CAR_TYPE IN ('세단', 'SUV')
)

 

 

📌 설명:

• CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 CAR_ID 기준으로 RIGHT JOIN하여 자동차 정보와 대여 이력을 결합.
• CAR_TYPE이 '세단' 또는 'SUV'인 자동차만 필터링 (IN 함수 사용)
• 30일간 대여 금액(FEE1)을 계산 (DAILY_FEE * 30) : 조건이 11월 1일 ~ 30일 총 30일 대여.
• CASE WHEN을 이용해 2022년 11월 1일 ~ 30일 사이에 대여 이력이 있으면 '대여불가', 없으면 '대여가능'으로 분류.

 

🔹 최종 SELECT 문

SELECT DISTINCT R.car_id, R.CAR_TYPE,
                 ROUND(FEE1 * (100 - DISCOUNT_RATE) / 100) FEE
FROM RANTAL_CAR R
LEFT JOIN (
    SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE duration_type = '30일 이상'
) P ON R.CAR_TYPE = P.CAR_TYPE
WHERE CAR_ID NOT IN (SELECT CAR_ID FROM RANTAL_CAR WHERE YN = '대여불가')
      AND FEE1 * (100 - DISCOUNT_RATE) / 100 BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

 

📌 설명:

1. RANTAL_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 CAR_TYPE 기준으로 LEFT JOIN.
2. 할인 정책에서 DURATION_TYPE이 '30일 이상'인 데이터만 가져옴 : 빌리는 조건이 30일 이상 (11/1~11/30)
3. 대여 가능(YN = '대여가능')한 자동차만 선택.
   • CAR_ID NOT IN (SELECT CAR_ID FROM RANTAL_CAR WHERE YN = '대여불가')
   • CAR_ID IN (SELECT CAR_ID FROM RANTAL_CAR WHERE YN = '대여가능') 으로 코드 사용하게 되면 한 차량이 대여가능, 대여가능, 대여불가 일 경우 대여가능으로 처리 될 가능성 있음 
   • 대여 히스토리가 2개 이상일 때 하나라도 대여불가라면 그 차량은 대여 불가차량
4. 30일 대여 금액을 할인율(DISCOUNT_RATE)을 적용하여 계산.
   • FEE1 * (100 - DISCOUNT_RATE) / 100 이 50만 원 이상, 200만 원 미만인 자동차만 선택.
5. 정렬 조건 적용:
   • FEE 기준 내림차순
   • 같은 FEE일 경우 CAR_TYPE 오름차순
   • CAR_TYPE까지 같을 경우 CAR_ID 내림차순

---

✅ 정리

1. WITH 절을 이용해 대여 가능 여부와 30일 대여 금액을 계산하는 테이블(RANTAL_CAR) 생성.
2. '30일 이상' 할인율 정보를 적용하기 위해 CAR_TYPE 기준으로 LEFT JOIN 수행.
3. 대여 가능 차량 중 할인율을 적용한 최종 금액이 50만 원 이상, 200만 원 미만인 데이터만 필터링.
4. 요구 조건에 맞춰 정렬하여 결과 출력.