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

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

 

문제

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

 

이 문제 매우매우 어려웠다. 실증나서 한동안 이문제 보지도 않았다. 결국 다른 사이트 도움을 받았다. 문제를 하나하나 살펴보자. 

여기서 중요한 점은 2022년 11월 30일까지 대여 가능 부분이다.  아래 쿼리를 보자

SELECT
    *
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
WHERE
    START_DATE <= "2022-11-30" AND END_DATE >= "2022-11-01"

 

쿼리 결과문

결론부터 말한다. 위 쿼리는 2022년 11월 중 단 하루라도 대여기간이 있는 자동차를 말한다. 다시 강조하지만 우리가 찾는 조건 중 하나가 2022년 11월 30일까지 대여 가능이다. 즉 저부분은 필요가 없다. 내가 해깔린 부분은 11월 어느 날이라도 대여가 가능한 자동차를 찾는 줄 알았다. 멍청한 생각이었다. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한" 차량이다. 이는 11월 전체 기간(11월 1일부터 30일까지) 동안 연속적으로 대여 가능한 차량을 의미한다. 보수적으로 생각해도 문제가 모호한 부분이 있다. 이 점은 아쉽다. 

1. 시작일(START_DATE)이 2022-11-30 이전인 대여 기록을 찾습니다.
   START_DATE <= '2022-11-30'
   
   |------ 11월 이전에 시작 ------|------ 11월 중 시작 ------|
   01-01                         11-01                    11-30    12-31
   
2. 종료일(END_DATE)이 2022-11-01 이후인 대여 기록을 찾습니다.
   END_DATE >= '2022-11-01'
   
   |-- 11월 이전에 종료 --|------ 11월 중 종료 ------|-- 11월 이후에 종료 --|
   01-01                11-01                      11-30                12-31
   
3. 두 조건을 AND로 결합하면:
   |----------------- 11월과 겹치는 모든 대여 기록 -----------------|
   
   이런 상황들이 포함됩니다:
   - 11월 이전에 시작해서 11월 중에 종료
   - 11월 이전에 시작해서 11월 이후에 종료
   - 11월 중에 시작해서 11월 중에 종료
   - 11월 중에 시작해서 11월 이후에 종료

그래서 자동차 종류가 '세단' 또는 'SUV'인 조건과 11월 내내 빌릴 수 있는 자동차라는 조건을 결합하면

SELECT
    C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN
    CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
WHERE
    C.CAR_ID NOT IN (
        SELECT 
            CAR_ID
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE
            START_DATE <= "2022-11-30" AND END_DATE >= "2022-11-01"
    )
    AND CAR_TYPE IN ("SUV","세단")

이런 쿼리가 나온다. 이 조건을 하나의 가상 테이블로 만든다. WITH절을 써서 이 가상 테이블 이름을 TEMP로 둔다. 

WITH TEMP AS (SELECT
    C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN
    CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
WHERE
    C.CAR_ID NOT IN (
        SELECT 
            CAR_ID
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE
            START_DATE <= "2022-11-30" AND END_DATE >= "2022-11-01"
    )
    AND CAR_TYPE IN ("SUV","세단")         
             )

그 다음 TEMP 테이블을 바탕으로 중복되는 자동차는 제거하고 할인율을 적용한 비용을 계산한다.

TEMP2 AS (
    SELECT 
            DISTINCT T.CAR_ID, T.CAR_TYPE,
            ROUND(T.DAILY_FEE * (1 -(DISCOUNT_RATE / 100))) * 30 AS FEE
    FROM    
            TEMP T
    JOIN    
            CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON T.CAR_TYPE = P.CAR_TYPE
    WHERE
            DURATION_TYPE = "30일 이상"
)

이러면 이렇게 된다.

아래는 최종쿼리

WITH TEMP AS (SELECT
    C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN
    CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
WHERE
    C.CAR_ID NOT IN (
        SELECT 
            CAR_ID
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE
            START_DATE <= "2022-11-30" AND END_DATE >= "2022-11-01"
    )
    AND CAR_TYPE IN ("SUV","세단")         
             ),
TEMP2 AS (
    SELECT 
            DISTINCT T.CAR_ID, T.CAR_TYPE,
            ROUND(T.DAILY_FEE * (1 -(DISCOUNT_RATE / 100))) * 30 AS FEE
    FROM    
            TEMP T
    JOIN    
            CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON T.CAR_TYPE = P.CAR_TYPE
    WHERE
            DURATION_TYPE = "30일 이상"
)
SELECT 
    *
FROM 
    TEMP2
WHERE
    FEE >= 500000 AND FEE <= 2000000