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

---

Table 소개 , 편의상 1번 2번 3번 테이블

1번 테이블 : CAR_RENTAL_COMPANY_CAR : 대여 중인 자동차들의 정보

 

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

 

2번 테이블 : CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보

 

3번 테이블 : CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 별 대여 기간 종류 별 할인 정책 정보

 

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

---

문제

- 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명 : FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성.

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

 

---

풀이 순서

car_type 트럭에 대해서 총 비용 (FEE) 구하는 법

= 하루 대여 비용(daily_Fee) * 며칠간 빌렸는지(strat_date와 end_date로 구하기) * 할인율(duration_type에 따른 discount_rate)

 

1. 트럭 대여 기록 집계 하기 

-> 1,2 번 테이블 inner join + car_type 조건

 

2. 며칠간 빌렸는지(strat_date와 end_date로 구하기) 집계하기 -> period

-> datediff + 1 (+1 안하면 하루 빌린 건 0이라고 집계됨)

 

3. 기록 ID와 금액 리스트를 최종적으로 출력해야함

-> history_id, 금액(daily_fee, period)

 

4. 3번 테이블의 car_type과 매칭시키기 위해 select car_type

 

5. duration_type 생성

-> 3번 테이블의 duration_type과 매칭시켜서, discount_rate를 활용하기 위해

 

WITH a AS (
    SELECT
        c.daily_fee
        ,c.car_type
        ,h.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 between 30 and 89 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date) + 1 between 7 and 29 THEN '7일 이상'
        ELSE 'NONE'
    END AS duration_type
FROM
    car_rental_company_rental_history AS h
    JOIN
    car_rental_company_car AS c
    ON c.car_id = h.car_id
WHERE
    c.car_type = '트럭')

 

 

 

 

최종적으로 기록 ID와 금액 리스트를 출력해야합니닷

 

5. left join으로 with 문으로 정의한 a 테이블과 3번 테이블 join시키기

- duration_type, car_type

-> 매칭된 discount_rate를 사용하기 위함!

 

왜 left join 인가?

->

자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 문제를 풀어야함!

a는 트럭 대여 기록에 대한 정보를 담고 있기 때문에 이걸 보존하면서 join 해야함.

 

6. FEE 집계 하기

-> discount_rate는 정수 형태로 표현되어있음. ex) 5

-> 하루대여비용 * 빌린기간 * 할인받은 값이 되도록 비율 곱해주기 

 

IFNULL(A,B) : A가 NULL 이면 B로 대치해줌

discount_rate가 null 이면 0으로 대치해줌으로써 할인 효과가 없도록 해주기

 

null이 되는 이유?

a를 기준으로 p를 left join 하기 때문에 p에서는 null 값이 발생할 수 있음. 

7일 이상, 30일 이상, 90일 이상 에 포함 안되는 ( 6일 이하 ) 값들은 매핑시 discount_rate가 null 값이 됨

 

7. 순서 정렬하기 order by

 

SELECT
    a.history_id, 
    ROUND(a.daily_fee * a.period * (100 - IFNULL(p.discount_rate,0)) * 0.01) AS FEE
FROM
    a
    LEFT JOIN
    car_rental_company_discount_plan AS p
    ON p.duration_type = a.duration_type 
    AND p.car_type = a.car_type
ORDER BY
    FEE DESC
    ,a.history_id DESC

 

 

---

정답

WITH a AS (
    SELECT
        c.daily_fee
        ,c.car_type
        ,h.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 between 30 and 89 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date) + 1 between 7 and 29 THEN '7일 이상'
        ELSE 'NONE'
    END AS duration_type
FROM
    car_rental_company_rental_history AS h
    JOIN
    car_rental_company_car AS c
    ON c.car_id = h.car_id
WHERE
    c.car_type = '트럭')   

SELECT
    a.history_id, 
    ROUND(a.daily_fee * a.period * (100 - IFNULL(p.discount_rate,0)) * 0.01) AS FEE
FROM
    a
    LEFT JOIN
    car_rental_company_discount_plan AS p
    ON p.duration_type = a.duration_type 
    AND p.car_type = a.car_type
ORDER BY
    FEE DESC
    ,a.history_id DESC