코드카타를 진행하면서,
배운 적 없는 함수를 사용하는 건 분명 아닌데 나에게 처음으로 좌절을 주었던 문제.

문제

74번.

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를 기준으로 내림차순 정렬해주세요.

(링크)

테이블 이해

CAR_RENTAL_COMPANY_CAR 대여 중인 자동차 정보
CAR_RENTAL_COMPANY_RENTAL_HISTORY 자동차 대여 기록 정보
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 자동차 종류별 대여 기간 종류별 할인 정책 정보
할인율 적용되는 대여 기간 종류 : 7일 이상, 30일 이상, 90일 이상 (7일 미만은 할인 x)

문제 해석

1) 자동차 종류가 ‘세단’ or ‘SUV’
2) 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 (== 2022년 11월 통째로 대여 예정)
3) 30일간의 대여 금액이 50만원 이상 200만원 미만
4) 자동차 ID, 자동차 종류, 대여 금액(as ‘Fee’) 조회
5) 대여 금액 기준 desc, 자동차 종류 asc, 자동차 ID asc

주요 요소

대여 금액(fee) = 30 daily_fee (1-(discountrate/100))
대여 가능 기간 계산 : 11월 중에 아예 대여 기록이 없어야 함
= 11월 이전에 대여와 반납이 이루어지거나, 11월 이후에 대여와 반납이 이루어지거나 = 11월 이후에 대여가 시작되거나(start_date), 11월 이전에 반납이 끝나거나 (end_date)
= not(start_date <= ‘2022-11-30’ OR ‘2022-11-01’ <= end_date)

날짜 조건에 대한 부분이 많은 사람들을 괴롭힌 문제였다 (적어도 우리 조에서는)
동일한 car_id에 대해 여러 대여 기록이 존재하기 때문에,
코드 작성 초반부에 WHERE절을 작성했다고 하더라도, JOIN 과정에서 car_id를 제대로 제외하지 못하면 결과에 불순물이 포함되는 경우가 발생한다.
아래 실행 결과 중 SUV가 포함된 결과를 보고 있다면, car_id를 제대로 걸러내지 못했을 가능성이 높다.

나 같은 경우엔 아래와 같은 과정을 거쳐 이 문제를 해결했다.

# 1. 2022년 11월 중 대여가 불가능한 car_id 조회 -> 이후 제외할 것
SELECT
    car_id
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE
    (start_date <= '2022-11-30') 
    	AND ('2022-11-01' <= end_date)

# 2. 1에서 추출한 car_id를 제외한 car_id만 남기기
SELECT
    car_id
FROM 
    CAR_RENTAL_COMPANY_CAR 
WHERE 
    car_id NOT IN (
    SELECT
        car_id
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE
        (start_date <= '2022-11-30') 
    	AND ('2022-11-01' <= end_date)
    )
ORDER BY
    car_id
;

최종 코드

SELECT
    distinct c.car_id
    , c.car_type
    , round((daily_fee * (1-(d.discount_rate/100)) * 30), 0) as 'FEE'
FROM
    CAR_RENTAL_COMPANY_CAR c
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
        ON c.car_id = h.car_id
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
        ON c.car_type = d.car_type
WHERE
    c.car_type in ('세단', 'SUV')
    AND c.car_id NOT IN (
        SELECT
            car_id
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE
            (start_date < '2022-11-30') 
            AND ('2022-11-01' < end_date)
        )
    AND d.duration_type = '30일 이상'
    AND ((daily_fee * (1-(d.discount_rate/100)) * 30) between 500000 and 2000000)
ORDER BY
    3 desc
    , 1
    , 2 desc
;