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

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

문제

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_id, car_type, fee

조건 세단/SUV, 11월 간 대여 가능 자동차, 대여 금액이 50~200인 차

정렬 문제 참고

 

1. 각 테이블을 전체적으로 보고 구조를 파악한다.

<3개 테이블 정보를 모두 쓰니 join이 두 번은  들어갈 것!>

2. 각 테이블 별로 적용할 조건을 생각해보자

CAR_RENTAL_COMPANY_DISCOUNT_PLAN는 REGEXP나 IN을 활용해 세단과 SUV를 불러야 되겠구나

CAR_RENTAL_COMPANY_RENTAL_HISTORY는 11월에 대여가 가능하다는 것을 어떻게 알고 추출하지?

세단과 SUV의 할인율이 다르네?

각 자동차별로 Daily_fee가 다르니까 대여 금액에 차이가 생기겠구나

3. 각 테이블에 조건을 달아 필터링을 한 후 JOIN을 해본다.

Daily_fee를 계산하기 위해  CAR_RENTAL_COMPANY_CAR와 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을

CAR_RENTAL_COMPANY_CAR에선 세단과 SUV만 부른 테이블을,

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 세단과 SUV 그리고 30일 플랜만 추출한 테이블을 JOIN한다

discount_rate와 daily_fee를 이용해 30일 간의 이용 요금을 계산한 FEE 컬럼을 만든다

CAR_RENTAL_COMPANY_RENTAL_HISTORY의 대여 가능 날짜 조건을 필터링한 후 JOIN한다.

마지막으로 FEE의 범위를 지정해준다.

4. 문제에서 지시한대로 정렬해준다

 

* 문제를 푸는 방법은 정말 다양하게 있는데,

나는 가독성과 활용성을 위해 가상 테이블(View)를 생성하여

"각 자동차별 30일 플랜 이용 요금 테이블"을 만들어주었다.

가상 테이블을 만드는 기준 중 하나는 정보가 실시간으로 바뀌지 않아

많은 갱신이 필요하지 않은 경우를 생각했다.

반대로 이번 문제의 history 즉, 대여 가능 여부가 실시간으로 바뀌는 테이블일 경우

매번 쿼리로 불러와서 갱신된 정보를 사용해야한다. 

 

이를 구현한 코드는 아래와 같다

with days_30_plan AS
(SELECT  C.car_id, C.car_type, ROUND((1- P.discount_rate*0.01) * C.daily_fee * 30) AS FEE
FROM Car_rental_company_car AS C 
INNER JOIN
(SELECT * FROM Car_rental_company_discount_plan
WHERE duration_type = "30일 이상" AND car_type REGEXP("세단|SUV")) AS P
ON C.car_type = P.car_type)
-- 잘 변하지 않을 것 같은 테이블을 계속 불러서 쓸 수 있도록 View 형태로 생성

SELECT d.car_id, d.car_type, d.FEE  FROM days_30_plan AS d 
RIGHT JOIN -- RIGHT TABLE : 매일 갱신되는 대여 가능 여부 테이블
(SELECT * FROM Car_rental_company_rental_history
GROUP BY car_id
HAVING MAX(end_date) < "2022-11-01") AS h -- 대여 기록은 시계열이기 때문에 가장 최근 주문건만 반영!
ON d.car_id = h.car_id
WHERE FEE >= 500000 AND FEE < 2000000
ORDER BY d.FEE DESC, d.car_type, d.car_id DESC

 

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

프로그래머스

GOOD