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

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는

       프로그래머스 코딩테스트 연습,   

      https://school.programmers.co.kr/learn/challenges?page=1&languages=mysql%2Coracle임을 밝힙니다.

      · 본 문제 https://school.programmers.co.kr/learn/courses/30/lessons/157339

---

■ 문제

문제 설명

 다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다. 

 CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

Column name	Type	Nullable
CAR_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DAILY_FEE	INTEGER	FALSE
OPTIONS	VARCHAR(255)	FALSE

 자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.

 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DUTAION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

Column name	Type	Nullable
PLAN_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DURATION_TYPE	VARCHAR(255)	FALSE
DISCOUNT_RATE	INTEGER	FALSE

---

문제 

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

---

■ 풀이

  문제 요구사항  

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 ¹

2022년 11월 1일부터 2022년 11월 30일까지 대여 가능²하고

30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 ³에 대해서                       < 내용조건 1, 2, 3

자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성 

결과는 대여 금액을 기준으로 내림차순 정렬하고,                                                             < 정렬조건1

대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬,                                      < 정렬조건2

자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬                                   < 정렬조건3

---

내용조건2 에 해당하는 CAR_ID를 뽑아내는 것이 관건인 문제였다.

처음엔 START_DATE와 END_DATE를 WHERE절 OR 조건으로 두고 출력해봤는데 조건에 부합하는 CAR_ID가 출력되지 않는 걸 확인했다.

 

 그래서  세단이거나 SUV 인 데이터값¹을 CAR_ID로 그룹핑해서 가장 최근 시작날짜가 최소 12월 1일이거나 최근 대여 종료 날짜가 10월 31일인 CAR_ID를 포함하는² 이라는 조건을 메인쿼리 WHERE절 조건 1

할인율(CAR_TYPE별 대여기간 30일 이상 조건)을 메인쿼리 WHERE절 조건 2 넣어주었다.

 

SELECT절에서 출력되는 CAR_ID별 FEE를 계산하고 난 후에 HAVING절에서 조건  ³ 

적용하고 마지막으로 정렬조건 1~3을 적용해주었다.

SELECT DISTINCT CRCC.CAR_ID AS CAR_ID
    , CRCC.CAR_TYPE AS CAR_TYPE
    , FLOOR(CRCC.DAILY_FEE * 30 * (0.01*(100- CRCDP.DISCOUNT_RATE))) AS FEE
 FROM CAR_RENTAL_COMPANY_CAR CRCC
 
 LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
 ON CRCC.CAR_ID = CRCRH.CAR_ID
 
 LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CRCDP
 ON CRCC.CAR_TYPE = CRCDP.CAR_TYPE
 
 WHERE CRCC.CAR_ID IN (SELECT CH.CAR_ID
                  FROM CAR_RENTAL_COMPANY_CAR CC
                  LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY CH
                  ON CH.CAR_ID = CC.CAR_ID
                  WHERE CC.CAR_TYPE IN ('세단','SUV')
                  GROUP BY 1
                  HAVING MAX(START_DATE) > '2022-11-30 23:59:59'
                  OR MAX(END_DATE) < '2022-11-01 00:00:00')
                  
AND CRCDP.DURATION_TYPE = '30일 이상'
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY 3 DESC, 2, 1 DESC
;