[Oracle][Programmers] 특정 기간동안 대여 가능한 자동차들의 대여 비용 구하기

1. 문제

프로그래머스

 

CAR_RENTAL_COMPANY_CAR 테이블

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

이 문제에는 총 3개의 테이블이 존재하니 각 테이블에서 필요한 정보를 잘 파악하는 것이 중요하다. 문제의 요구사항은 다음과 같다.

• 자동차 종류가 '세단' 또는 'SUV'인 자동차
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
• 30일간의 대여 금액이 50만원 이상 200만원 미만
• 자동차 ID(CAR_ID), 자동차 종류(CAR_TYPE), 30일간의 대여 금액(컬럼명 : FEE)
• 결과는 대여 금액(FEE) 기준 내림차순 정렬, 대여 금액이 같다면 자동차 종류(CAR_TYPE)를 기준으로 오름차순 정렬, 자동차 종류까지 같다면 자동차 ID(CAR_ID) 기준 내림차순 정렬

30일간의 대여 금액(FEE)은 자동차별 일일 대여 금액에 30일 대여 기간의 할인율(DISCOUNT_RATE)을 적용하여 구해야 한다. 다음과 같다.

자동차별 일일 요금(DAILY_FEE) * 30 * ((100 - DISCOUNT_RATE) / 100) AS fee

2. 풀이

테이블이 많이 나오고 요구사항이 많을수록 하나씩 하나씩 조합해가는 것이 중요하다. 

2-1. 자동차 종류가 '세단' 또는 'SUV'인 자동차

SELECT *
FROM car_rental_company_car c
WHERE c.car_type IN ('세단', 'SUV')

2-2. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능

난 이 조건을 설정하는 것이 꽤나 까다로웠다. 내가 처음에 했던 방법은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일(START_DATE)이 11월 이후이거나 대여 종료일(END_DATE)이 11월 이전인 경우의 행만을 추출했던 것이다. 이는 완전히 잘못된 접근이었다. 각 자동차별 여러 대여 기록이 있을텐데 그 중 11월에 해당하지 않는 기록만을 뽑는 것은 아무런 의미가 없기 때문이다.

그래서 다음과 같이 접근법을 수정했다. 대여 기록 테이블에서 11월에 대여 기록이 있는 자동차들의 ID를 추출하고, 이 ID 목록에 없는 자동차들만을 대상으로 하는 것이다. 이렇게 하면 11월에 대여 가능한 자동차들의 ID를 얻을 수 있음은 물론, 아예 대여 기록이 없는 자동차들의 ID도 같이 얻을 수 있다.

SELECT *
  FROM car_rental_company_car c
 WHERE c.car_id NOT IN (
    SELECT car_id
      FROM car_rental_company_rental_history
     WHERE TO_CHAR(start_date, 'YYYYMM') <= '202211' -- 11월에 대여 기록이 있는 자동차
       AND TO_CHAR(end_date, 'YYYYMM') >= '202211'
       )

2-3. 30일간의 대여 금액이 50만원 이상 200만원 미만

30일간의 대여 금액을 구하는 것이므로 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 DURATION_TYPE이 '30일 이상'인 데이터만 기준으로 삼으면 된다.

SELECT *
FROM (
    SELECT c.car_id, c.car_type, c.daily_fee * 30 * ((100-p.discount_rate)/100) AS fee
      FROM car_rental_company_car c
      JOIN car_rental_company_discount_plan p
        ON c.car_type = p.car_type
       AND p.duration_type = '30일 이상'
)
WHERE fee BETWEEN 500000 AND 2000000-1

2-4. 결과는 대여 금액(FEE) 기준 내림차순 정렬, 대여 금액이 같다면 자동차 종류(CAR_TYPE)를 기준으로 오름차순 정렬, 자동차 종류까지 같다면 자동차 ID(CAR_ID) 기준 내림차순 정렬

SELECT *
FROM (...)
ORDER BY fee DESC, car_type, car_id DESC

2-5. 최종 쿼리 💡

위 내용을 조합한 최종 쿼리는 다음과 같다.

SELECT *
FROM (
    SELECT c.car_id, c.car_type, c.daily_fee * 30 * ((100-p.discount_rate)/100) AS fee
      FROM car_rental_company_car c
      JOIN car_rental_company_discount_plan p
        ON c.car_type = p.car_type
       AND p.duration_type = '30일 이상'
     WHERE c.car_id NOT IN (
           SELECT car_id 
             FROM car_rental_company_rental_history
            WHERE TO_CHAR(start_date, 'YYYYMM') <= '202211' 
              AND TO_CHAR(end_date, 'YYYYMM') >= '202211'
           )
       AND c.car_type IN ('세단', 'SUV')
)
WHERE fee BETWEEN 500000 AND 2000000-1
ORDER BY fee DESC, car_type, car_id DESC