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

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

 

 

 

WITH RENT_INFO AS (
    SELECT HISTORY_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS RENT_DAYS, DAILY_FEE, CAR_TYPE,
       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 '' END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
    WHERE CAR_TYPE = '트럭'
)

SELECT RENT_INFO.HISTORY_ID, 
       ROUND(RENT_INFO.DAILY_FEE * RENT_INFO.RENT_DAYS * (100 - IFNULL(PLAN.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM RENT_INFO LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
    ON PLAN.DURATION_TYPE = RENT_INFO.DURATION_TYPE AND PLAN.CAR_TYPE = RENT_INFO.CAR_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC;

 

 

 

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

프로그래머스