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

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

 

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 

자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 

대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 

대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 

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

 

풀이

1. car 와 history 테이블을 inner join

 

2. plan 테이블과 left join

-> history 테이블에서 start date 와 end date 사이의 날짜를 datediff 로 연산하여 대여기간을 구하고 case when을 이용하여이 대여기간에 따라 duration_type 과 매칭하여 join 하면 각 대여기간에 맞는 할인율이 칼럼으로 들어간다

 

이때 날짜를 +1 하는거에 주의하자,

예제를 보면 start date: 08-03, end date: 08-04의 대여기간을 2일로 계산한다

양아치

 

3. 요금을 계산하여 fee 칼럼에 넣는다

fee = 기간 * daily_fee * 할인율

할인율 = (100 - discount_rate) / 100

-> 만약 대여기간이 7일 미만이라면 매칭된 할인 플랜이 없게되고 p.discount_rate 는 null 값을 가지게 된다. 이 경우 discount_rate는 0으로 계산한다

그리고 최종으로 나온 fee는 소수점이 포함되어있기에 floor 로 내림 연산을 해준다

 

4. where 을 이용하여 조건 걸고 order by 이용하여 순서 조건 맞춰주면 끝

 

select h.history_id, 
    floor((datediff(h.end_date, h.start_date)+1) * c.daily_fee * (100-ifnull(p.discount_rate, 0))/100) as fee
from car_rental_company_car c
    join car_rental_company_rental_history h on c.car_id = h.car_id
    left join car_rental_company_discount_plan p on c.car_type = p.car_type and p.duration_type =
        case when datediff(h.end_date, h.start_date)+1 between 7 and 29 then "7일 이상"
        when datediff(h.end_date, h.start_date)+1 between 30 and 89 then "30일 이상"
        when datediff(h.end_date, h.start_date)+1 >= 90 then "90일 이상" end
where c.car_type = '트럭'
order by fee desc, h.history_id desc