[SQL] 프로그래머스 String, Date : 자동차 대여 기록 별 대여 금액 구하기(level 4) - 2회차 복습 완료

💡문제 링크

프로그래머스

 

 

💡SQL 코드 설계

나는 계속 행을 조작할 생각을 했는데, chatGPT가 열을 조작하는 방법을 알려줬다.

 

가장 까다로웠던 건 할인율을 어떻게 처리할지의 문제

1. history와 car 테이블을 합쳐 '트럭'인 행만 남긴다.

2. history와 plan 테이블을 합쳐 할인정책을 적용한 대여 비용을 추출한다. - 이게 어렵다.

일단 history와 car 테이블을 join해서 합친 모습은 대충 이런 모양이었다.

history_id	car_id	car_type	daily_fee	start_date	end_date

여기에다가 plan 테이블을 세 번 조인해서 세 개의 열을 더 만드는 거다.

LEFT JOIN car_rental_company_discount_plan AS dp_90
    ON c.car_type = dp_90.car_type AND dp_90.duration_type = '90일 이상'
LEFT JOIN car_rental_company_discount_plan AS dp_30
    ON c.car_type = dp_30.car_type AND dp_30.duration_type = '30일 이상'
LEFT JOIN car_rental_company_discount_plan AS dp_7
    ON c.car_type = dp_7.car_type AND dp_7.duration_type = '7일 이상'

 

 

챗지피티는 LEFT JOIN을 사용했지만 그냥 JOIN을 사용해도 무방하다.

그러면 결과는 다음과 같이 된다.

그래서 대여 기간을 계산해봤을 때 90일 이상이면 dp_90.discount_rate 열의 값(10%)를 사용하고, 30일 이상이면 dp_30.discount_rate 열의 값(7%)를 사용하고, 7일 이상이면 dp_7.discount_rate 열의 값(5%)를 사용하면 된다.

예를 들어 history_id 1의 경우 대여 기간이 7일이니까 5% 할인을 적용하는 거다.

C.DAILY_FEE * (DATEDIFF(END_DATE, START_DATE) + 1) * DP_7.DISCOUNT_RATE AS FEE

어떻게 이런 기가막힌 아이디어를 생각해냈지..chatGPT 대단하다..

 

💡SQL 코드

SELECT h.history_id,
    FLOOR(
        CASE
            -- 90일 이상인 경우
            WHEN DATEDIFF(h.end_date, h.start_date) + 1 >= 90 THEN c.daily_fee * (DATEDIFF(h.end_date, h.start_date) + 1) * (1 - dp_90.discount_rate / 100)
            -- 30일 이상 90일 미만인 경우
            WHEN DATEDIFF(h.end_date, h.start_date) + 1 >= 30 THEN c.daily_fee * (DATEDIFF(h.end_date, h.start_date) + 1) * (1 - dp_30.discount_rate / 100)
            -- 7일 이상 30일 미만인 경우
            WHEN DATEDIFF(h.end_date, h.start_date) + 1 >= 7 THEN c.daily_fee * (DATEDIFF(h.end_date, h.start_date) + 1) * (1 - dp_7.discount_rate / 100)
            -- 7일 미만인 경우
            ELSE c.daily_fee * (DATEDIFF(h.end_date, h.start_date) + 1)
        END
    ) AS fee
FROM car_rental_company_rental_history AS h
JOIN car_rental_company_car AS c
    ON c.car_id = h.car_id
-- 각 기간별 할인율을 가져오기 위해 세 번의 JOIN 사용
LEFT JOIN car_rental_company_discount_plan AS dp_90
    ON c.car_type = dp_90.car_type AND dp_90.duration_type = '90일 이상'
LEFT JOIN car_rental_company_discount_plan AS dp_30
    ON c.car_type = dp_30.car_type AND dp_30.duration_type = '30일 이상'
LEFT JOIN car_rental_company_discount_plan AS dp_7
    ON c.car_type = dp_7.car_type AND dp_7.duration_type = '7일 이상'
WHERE c.car_type = '트럭'
ORDER BY fee DESC, h.history_id DESC;

💡기억할 내용

1. 하나의 테이블에 대해 조인을 여러 번 적용할 아이디어 

2. 기간을 구할 때에는 DATEDIFF() + 1! 1을 더해줘야 한다.