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

[문제]
https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

 

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

[단계별 풀이]

1. 출력해야 하는 컬럼을 보자. 자동차 ID, 자동차 종류, 그리고 대여 금액을 구해야한다.

자동차 ID와 자동차 종류는 CAR_RENTAL_COMPANY_CAR 테이블에서 가져올 수 있고,

대여금액은 1일 가격과 기간, 할인율 정보가 필요한데  이는 

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 에서 가져올 수 있다.

 

2. 두 테이블을 먼저 병합하자. 모든 자동차들에 대해서 출력해야 하므로 LEFT JOIN을 해도 되지만 두 테이블이 모든 타입정보를 포함하고 있으므로 INNER JOIN을 해도 된다. CAR_TYPE 기준으로 병합하자

병합한 테이블

3. 다음 조건을 보자. 

a. 자동차 종류가 세단 또는 SUV인 자동차를 필터링 해야하고,

b. 2022년 11월 달 내내 대여가 가능해야 하고

c. 30일 간의 대여 금액이 50만원 이상 200만원 미만이어야 한다.

 

a 번 조건은 앞서 테이블을 병합할 때 JOIN 조건에 추가해주자.

b 번 조건을 확인하기 위해서는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 기록이 정보를 가져와야 한다. 앞서 병합한 테이블과 또 다시 병합해주자. 기록이 없는 자동차들도 있을 수 있으므로 여기서는 LEFT JOIN을 해야 한다.

c 번 조건은 30일 간의 대여 금액을 구하는 것이므로 우리에게 필요한 할인율은 30일 이상에 해당하는 할인율이다. a번 조건과 함께 ON 조건절에서 처리해주자

JOIN조건 추가 및 LEFT JOIN

 

b 번 조건에서 22년 11월 달 내내 대여가 가능한 자동차 ID를 필터링하려면 어떻게 해야할까?

우선 테이블을 살펴보자. (C.CAR_ID, START_DATE, END_DATE 출력 H.CAR_ID 로 정렬)

 

대여 기록이 없는 차량도 있고 대여기록이 많은 차량도 있다. 여기서 어떻게  11월 달 풀로 대여가 가능한 차들만 필터링 할 수 있을까? 답은 11월 달에 빌린 기록이 있는 애들만 필터링하면 된다.

 

CAR_RENTAL_COMPANY_RENTAL_HISTORY  테이블에서 11월 기록이 있는 행들만 필터링하고 그 상태로 조인을 하게 된다면 11월 기록이 있는 차량들에 대해서는 정상적으로 연결이 되고, 그렇지 않은 차량들은 LEFT JOIN 이므로 NULL값이 된다. 11월 기록이 있는 차량들의 특징은 START_DATE 가 11월 30일보다 같거나 작으면서 END_DATE가 11월 1일보다 크거나 작아야 한다. 이를 ON 조건에 추가해주자.

 

11월 달에 빌린 기록이 있는 차량들만 필터링 되어서 LEFT JOIN으로 연결되었다. 대여 기록 정보가 NULL인 차량들이 바로 11월 달에 풀로 빌릴 수 있는 차량들이다. 이를 WHERE 절에서 필터링 해주자.  (START_DATE IS NULL)

 

이제 제일 처음 살펴보았던 컬럼을 출력해주자.

CAR_ID 와 CAR_TYPE, 30일간의 대여금액을 구해준다.

 

대여금액 = 1일 요금 x 30  x (1 - 할인율)  DISCOUNT_RATE : 8 → 0.08로 조정

 

대여금액에 별칭을 붙여주고 별칭은 HAVING 절에서 사용이 가능하니 HAVING 절에서 대여금액 조건을 걸어주고 정렬하고 제출하자.

[정답]

SELECT 
    C.CAR_ID
    , C.CAR_TYPE
    , ROUND(C.DAILY_FEE * 30 * (1 - 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 C.CAR_TYPE IN ('SUV', '세단')
    AND P.DURATION_TYPE = '30일 이상'
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    ON C.CAR_ID = H.CAR_ID
    AND H.START_DATE <= '2022-11-30'
    AND H.END_DATE >= '2022-11-01'
WHERE H.START_DATE IS NULL
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC