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

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

 

WITH NOT_RENT AS 
    (
    SELECT RENT.CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT
    WHERE RENT.END_DATE >= '2022-11-01' AND RENT.START_DATE < '2022-12-01'
    ),
    
    DIS_RATE AS 
    (SELECT DIS.CAR_TYPE, DIS.DURATION_TYPE, (100-DIS.DISCOUNT_RATE)*0.01 AS DISCOUNT_RATE
     FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DIS
     WHERE DIS.DURATION_TYPE = '30일 이상')


SELECT *
FROM
    (
    SELECT CAR.CAR_ID, CAR.CAR_TYPE,
           CEILING(CAR.DAILY_FEE*DIS_RATE.DISCOUNT_RATE*30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR AS CAR
    LEFT JOIN DIS_RATE
    ON CAR.CAR_TYPE = DIS_RATE.CAR_TYPE
    WHERE CAR.CAR_TYPE IN ('세단', 'SUV')
    AND NOT EXISTS (SELECT *
               FROM NOT_RENT
               WHERE NOT_RENT.CAR_ID = CAR.CAR_ID)) AS FINAL
WHERE FINAL.FEE >= 500000 AND FINAL.FEE < 2000000
ORDER BY FEE DESC,CAR.CAR_TYPE ASC, CAR.CAR_ID ASC

 

해법 포인트 1 

- WITH 문으로 각 테이블에서 풀이에 필요한 데이터만 추출하고자 하였다. 문제가 꽤 복잡하여, 쿼리를 읽기 어렵고 쿼리 디버깅이 힘들어서 WITH문을 사용하였다. 

 

위의 코드를 문제에 따라서 하나씩 조각내서 보자.

 

'...자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고...'

CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 11월 1일부터 30일까지 대여가 가능한 차량을 찾는 식으로 접근하면 안된다. 하나의 차량이 기간마다 여러번 대여한 이력이 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 있기 때문이다. 어떤 차량이 대여 가능하고 대여가 불가능한지 차량 아이디로서 구분이 가능해야 한다. 해당 기간에 대여한 차량이 '대여가 불가능한 차량들'이라는 테이블로 저장해야 한다.  

WITH NOT_RENT AS 
    (
    SELECT RENT.CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT
    WHERE RENT.END_DATE >= '2022-11-01' AND RENT.START_DATE < '2022-12-01'
    )

 

해법 포인트 2

 

세단/SUV 데이터를 추출한 차량 정보 테이블과 할인율에 대한 테이블을 JOIN하여 FROM 절의 인라인 서브쿼리로 나타낸다. 

특히, 하루 비용 X 할인율 X 30일 로 나타낸 뒤, 이후에 정수로 표현하기 위해 CEILING 함수를 이용하였다. 

CEILING() : 해당 숫자보다 크거나 같은 최소 정수를 반환

    (
    SELECT CAR.CAR_ID, CAR.CAR_TYPE,
           CEILING(CAR.DAILY_FEE*DIS_RATE.DISCOUNT_RATE*30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR AS CAR
    LEFT JOIN DIS_RATE
    ON CAR.CAR_TYPE = DIS_RATE.CAR_TYPE
    WHERE CAR.CAR_TYPE IN ('세단', 'SUV')
    AND NOT EXISTS (SELECT *
               FROM NOT_RENT
               WHERE NOT_RENT.CAR_ID = CAR.CAR_ID)) AS FINAL

 

해법 포인트 3

위의 코드에서 WHERE 절에 NOT_RENT(대여가 안되는 차량 테이블 정보)와 차량 정보 테이블을 조인하여 서브 쿼리로 나타내어, 빌릴 수 있는 차량 정보만 추출한다.  

NOT EXISTS (SELECT *
               FROM NOT_RENT
               WHERE NOT_RENT.CAR_ID = CAR.CAR_ID)) AS FINAL

 

해법포인트 4

마지막으로, 연산한 FEE 열을 일정 금액 내에서 필터링 해야 하므로, 가장 밖의 쿼리문에서 WHERE 조건절을 걸어서 원하는 값을 산출한다. 

SELECT *
FROM ...
WHERE FINAL.FEE >= 500000 AND FINAL.FEE < 2000000
ORDER BY FEE DESC,CAR.CAR_TYPE ASC, CAR.CAR_ID ASC