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

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를 기준으로 내림차순 정렬해주세요.

 

풀이

-- 코드를 입력하세요
-- 트럭인 자동차의 대여기록별 금액 
-- 대여금액 내림차순, ID 내림차순


SELECT
    h.HISTORY_ID,
    ROUND(
        (DATEDIFF(h.END_DATE, h.START_DATE) + 1) * c.DAILY_FEE *
        (100 - IFNULL(p.DISCOUNT_RATE, 0)) / 100
    ) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN CAR_RENTAL_COMPANY_CAR c
    ON h.CAR_ID = c.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
    ON c.CAR_TYPE = p.CAR_TYPE
    AND (
        (DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 90 AND p.DURATION_TYPE = '90일 이상')
        OR
        (DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 30 AND DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 90 AND p.DURATION_TYPE = '30일 이상')
        OR
        (DATEDIFF(h.END_DATE, h.START_DATE) + 1 >= 7 AND DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 30 AND p.DURATION_TYPE = '7일 이상')
    )
WHERE c.CAR_TYPE = '트럭'
ORDER BY FEE DESC, h.HISTORY_ID DESC;