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

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, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

 

출처 : 프로그래머스

 

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

 

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

 

출처 : 프로그래머스

 

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

 

출처 : 프로그래머스

 

할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

 

문제

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_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면

 

출처 : 프로그래머스

 

출처 : 프로그래머스

 

출처 : 프로그래머스

 

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차는 자동차 ID가 1, 4, 5인 자동차입니다.

 

일일 대여 요금에 자동차 종류 별 대여기간이 30일 이상인 경우의 할인율을 적용하여 30일간의 대여 금액을 구하면,

 

• 자동차 ID가 1인 경우, 일일 대여 금액 25,000원 * 8% 할인율 * 30일 = 총 대여 금액 690,000원
• 자동차 ID가 4인 경우, 일일 대여 금액 12,000원 * 10% 할인율 * 30일 = 총 대여 금액은 324,000원(x)
• 자동차 ID가 5인 경우, 일일 대여 금액 22,000원 * 10% 할인율 * 30일 = 총 대여 금액은 621,000원
• 대여 금액이 50만원 이상 200만원 미만인 경우에 대해서 대여 금액을 기준으로 내림차순, 자동차 종류를 기준으로 오름차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

출처 : 프로그래머스

 

주의사항

FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

 

풀이

1. 문제에서 요구하는 조건은 크게 4가지이다.

 

 - '세단' 또는 'SUV' 인 CAR_TYPE

 - 2022년 11월1일 ~ 2022년 11월30일까지 대여 가능한 자동차 -> START_DATE, END_DATE

 - 대여 기간이 '30일 이상'이 적용되므로 DURATION_TYPE = '30일 이상' 인 것을 가져와야 한다.

 - 총 대여 비용(FEE) -> 50만 <= FEE < 200만

 

2. 총 대여 비용은 예시와 동일하게 구하면 된다. 이것을 FEE라고 정의해야 한다.

 

 - DAILY_FEE(일일 대여요금) * 30(일) * 할인율(%)

 - DAILY_FEE * 30 * ((100 - DISCOUNT_RATE) / 100)

 - 결과 예시의 FEE는 정수이므로, ROUND() 함수를 통해 정수로 변환해야 한다.

 

3. 3개 테이블의 컬럼이 모두 필요하므로 3개 테이블 JOIN

 

 - CAR_RENTAL_COMPANY_CAR <-> CAR_RENTAL_COMPANY_RENTAL_HISTORY (연결 컬럼 : CAR_ID)

 - CAR_RENTAL_COMPANY_CAR <-> CAR_RENTAL_COMPANY_DISCOUNT_PLAN (연결 컬럼 : CAR_TYPE)

 

4. (⭐) 2022년 11월 한 달 동안 대여가 가능해야 한다.

 

 - 여기서 주의해야 할 점은 11월 한 달이 대여 기간에 포함된 경우도 있다는 것이다.

 

 

 - 따라서, NOT IN 을 통해 11월 대여 기간이 포함된 CAR_ID를 제외하거나(방법 1)

 - 11월 이후에 반납한 자동차 중 12월에 대여하고 반납한 케이스만 제외하면 된다.

 

 

 - 가장 최근(마지막) 반납 날짜(END_DATE)가 11월1일 이전인지 확인(방법 2)해야 한다.

 - 문제에서 시간 값은 모두 동일하므로 START_DATE < END_DATE이다. 따라서 방법2의 경우 END_DATE만 확인하면 된다.

 

5. 자동차 별로 확인해야 하므로, CAR_ID로 그룹핑하고 자동차 별로 종류가 '세단' 또는 'SUV' 이고 총 대여 요금이 50만 이상 200만 미만인 것을 필터링하면 된다.

 

 - HAVING을 통해 위 조건들을 필터링하면 된다.

 - CAR_ID와 CAR_TYPE은 1대1이고 CAR_TYPE마다 FEE(총 대여 요금)이 결정되므로 (대여 기간이나 할인율은 고정) HAVING에서 수행할 수 있다.

 

정답 코드

SELECT a.CAR_ID, a.CAR_TYPE
, ROUND(a.DAILY_FEE * 30 * ((100-c.DISCOUNT_RATE) / 100)) as FEE
FROM CAR_RENTAL_COMPANY_CAR a
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c ON a.CAR_TYPE = c.CAR_TYPE
WHERE a.CAR_ID NOT IN (SELECT CAR_ID 
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE END_DATE > '2022-10-31' and START_DATE < '2022-12-01')
      and c.DURATION_TYPE = '30일 이상'
GROUP BY a.CAR_ID 
HAVING a.CAR_TYPE IN ('세단', 'SUV') and (FEE between 500000 and 1999999)
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

SELECT CAR_ID, CAR_TYPE, FEE
FROM (SELECT a.CAR_ID, a.CAR_TYPE, MAX(b.END_DATE) as MAX_END_DATE
      , ROUND(a.DAILY_FEE * 30 * ((100-c.DISCOUNT_RATE) / 100)) as FEE
      FROM CAR_RENTAL_COMPANY_CAR a
        JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
        JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c ON a.CAR_TYPE = c.CAR_TYPE
      WHERE c.DURATION_TYPE = '30일 이상'
      GROUP BY a.CAR_ID 
      HAVING a.CAR_TYPE IN ('세단', 'SUV') and (FEE between 500000 and 1999999)
     ) x
WHERE MAX_END_DATE < '2022-11-01'
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC