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

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

프로그래머스

코딩테스트 연습 > String, Date > 자동차 대여 기록 별 대여 금액 구하기

 

문제 분석

난이도: LEVEL4

 

문제 요구사항

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

 

정답 코드

WITH CTE AS (
    SELECT DISCOUNT_RATE, DURATION_TYPE	
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
)
SELECT H.HISTORY_ID, 
CONVERT((C.DAILY_FEE * H.DATE ) / 100 * (100 -
(CASE
    WHEN H.DATE >= 90 THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '90일 이상')
    WHEN H.DATE >= 30 THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '30일 이상')
    WHEN H.DATE >= 7  THEN (SELECT DISCOUNT_RATE FROM CTE WHERE DURATION_TYPE = '7일 이상')
    ELSE 0 
END)), UNSIGNED) FEE 
FROM 
CAR_RENTAL_COMPANY_CAR C JOIN (SELECT *, (DATEDIFF(END_DATE, START_DATE) + 1) DATE
                               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) H
ON C.CAR_ID = H.CAR_ID 
WHERE C.CAR_TYPE = '트럭'
ORDER BY 2 DESC, 1 DESC