프로그래머스 문제

---

테이블 구조

• CAR_RENTAL_COMPANY_CAR
  • CAR_ID : 자동차ID
  • CAR_TYPE : 자동차 종류
  • DAILY_FEE : 일일 대여 요금
  • OPTION : 자동차 옵션 리스트

• CAR_RENTAL_COMPANY_RENTAL_HISTORY
  • HISTORY_ID : 자동차 대여 기록 ID
  • CAR_ID : 자동차 ID
  • START_DATE : 대여 시작일
  • END_DATE : 대여 종료일

• CAR_RENTAL_COMPANY_DISCOUNT_PLAN
  • PLAN_ID : 각각 요금 할인 정책 ID
  • CAR_TYPE : 자동차 종류
  • DURATION_TYPE : 대여 기간 종류
  • DISCOUNT_RATE : 할인율

---

문제

자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

---

문제 분석

1. 자동차 종류가 '트럭'인 자동차

2. 대여 기록 별로 대여 금액(컬럼명: FEE) 기록 ID와 대여 금액 리스트를 출력

3. 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순

---

해결 방법

1. 자동차 종류가 '트럭'인 차를 구하기 위해서는 WHERE절에 조건을 입력해준다.

WHERE car.car_type = '트럭'

2. 대여 기록 별 대여 금액을 구하기 위해서는 각 종류별 대여기간(period), 일일 요금(daily_fee), 할인율(discount_rate)이 필요하다

위 이미지와 같이 자동차 종류별, 기간별로 할인율이 다르기 때문에 기간별로 JOIN을 시켜줘야 한다.

그렇기 때문에 car_rental_company_rental_history 테이블에서 DATEDIFF() + 1을 통해 대여 기간을 구해준다.

대여 기간을 구하고 해당 기간을 (7일 이상, 30일 이상, 90일 이상)으로 구분해 준뒤,

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블과 JOIN을 위해 새로운 컬럼을 생성해준 후 각 일일 요금, 자동차 종류, 대여 기록을 같이 출력하는 쿼리를 만들어 준다.

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')

위 쿼리를 실행하면 아래와 같은 결과가 나오게 된다.
Sub-Query로 편하게 사용하기 위해 with절안에 넣어주었다.

이후 car_rental_company_discount_plan 테이블과 JOIN을 통해 기록별 대여 금액을 계산해준다

SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC

이때 '트럭'으로만 분류하기 위해서 JOIN절에서 car_type 컬럼을 같이 JOIN을 시켜준다.

---

최종 코드

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   



SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC