[프로그래머스] 자동차 대여 기록 별 대여 금액 구하기

 

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(칼럼명:FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해 주세요.

결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해 주세요.

 

CAR_RENTAL_COMPANY_CAR

CAR_RENTAL_COMPANY_RENTAL_HISTORY

CAR_RENTAL_COMPANY_DISCOUNT_PLAN

 

---

풀이

1. 자동차 종류 : 트럭인 경우만 조건 입력(where 절)

WHERE car.CAR_TYPE = '트럭'

 

2. 대여 기록 별 대여 금액을 구하기 위해 필요한 것 확인

• 자동차 종류별 일일요금
• 자동차 종류별/대여기간별/할인율
• 대여 기간 : datediff()

SELECT  car.car_type, car.daily_fee, 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 = '트럭'

 

3. 2의 쿼리를 서브쿼리로 생성하여, 대여 금액 계산

• with 절 안에 2의 쿼리를 넣고
• CAR_RENTAL_COMPANY_DISCOUNT_PLAN과 조인
  • 이때, duration_type과 car_type이 일치하도록 조인 조건을 2개 걸어준다.
  • history_id 별로, fee를 계산 
    • duration_type이 None인 경우는 할인율 제외하고 계산하기 위해 case when을 사용

with value as(
SELECT  car.car_type, car.daily_fee, 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,
        case when value.duration_type = 'NONE' then value.daily_fee * value.period 
        else round(value.daily_fee * value.period * (100- plan.discount_rate)/100, 0) end 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

 

참고

• 다른 풀이에서는 case when을 쓰지 않고, IFNULL을 사용해서 NONE 인경우, 할인율이 0이 되게 만들었다.
• 이것도 기억해두면 좋겠다 👍

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