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

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

프로그래머스

-- 코드를 입력하세요
SELECT history.HISTORY_ID,
    ROUND(car.DAILY_FEE * (100 - IFNULL(discount.DISCOUNT_RATE, 0))/100 * history.days) FEE
FROM (SELECT *
      FROM CAR_RENTAL_COMPANY_CAR
      WHERE CAR_TYPE = '트럭') car
      JOIN 
      (SELECT CAR_ID, HISTORY_ID,
            DATEDIFF(END_DATE, START_DATE) + 1 days,
            (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일 이상'
            END) DURATION_TYPE
       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY history) history
      ON car.CAR_ID = history.CAR_ID
      LEFT JOIN 
      (SELECT *
       FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
       WHERE CAR_TYPE = '트럭') discount
      ON car.CAR_TYPE = discount.CAR_TYPE AND history.DURATION_TYPE = discount.DURATION_TYPE
ORDER BY fee DESC, history.history_id DESC