<프로그래머스> 자동차 대여 기록 별 대여 금액 구하기

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

프로그래머스

---

• 문제요구사항
  • 세 테이블을 조인하여 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서
  • 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력
  • 결과는 대여 금액을 기준으로 내림차순 정렬
  • 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

---

정답코드

1) MySQL

WITH RENT_HISTORY(HISTORY_ID, CAR_ID, TERM) AS
(
    SELECT HISTORY_ID,
           CAR_ID,
           DATEDIFF(END_DATE, START_DATE)+1 AS TERM
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
),
CAR_HISTORY(HISTORY_ID, TERM, CAR_ID, DAILY_FEE) AS
(
    SELECT  H.HISTORY_ID,
            H.TERM,
            H.CAR_ID,
            C.DAILY_FEE
    FROM RENT_HISTORY H LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
    WHERE C.CAR_TYPE = '트럭'
)
SELECT HISTORY_ID,
       ROUND(DAILY_FEE*DISCOUNT_RATE*TERM) FEE
FROM
(
    SELECT HISTORY_ID,
           DAILY_FEE,
           TERM,
           CASE WHEN TERM >= 90 THEN 0.85
                WHEN TERM BETWEEN 30 AND 89 THEN 0.92
                WHEN TERM BETWEEN 7 AND 29 THEN 0.95
                ELSE 1
                END DISCOUNT_RATE
    FROM CAR_HISTORY
) A
ORDER BY 2 DESC, 1 DESC;

---

2) Oracle

WITH RENT_HISTORY(HISTORY_ID, CAR_ID, TERM) AS 
(
    SELECT HISTORY_ID, 
           CAR_ID, 
           (END_DATE-START_DATE)+1 AS TERM
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
),
CAR_HISTORY(HISTORY_ID, TERM, CAR_ID, DAILY_FEE) AS 
(
    SELECT  H.HISTORY_ID, 
            H.TERM, 
            H.CAR_ID,
            C.DAILY_FEE
    FROM RENT_HISTORY H LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
    WHERE C.CAR_TYPE = '트럭'
)
SELECT HISTORY_ID, 
       ROUND(DAILY_FEE*DISCOUNT_RATE*TERM) FEE
FROM
(
    SELECT HISTORY_ID, 
           DAILY_FEE, 
           TERM, 
           CASE WHEN TERM >= 90 THEN 0.85
                WHEN TERM BETWEEN 30 AND 89 THEN 0.92
                WHEN TERM BETWEEN 7 AND 29 THEN 0.95
                ELSE 1
                END DISCOUNT_RATE
    FROM CAR_HISTORY
) A
ORDER BY ROUND(DAILY_FEE*DISCOUNT_RATE*TERM) DESC, HISTORY