[MY SQL] 기간별 할인율 가격에 반영하기

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] :  자동차 대여 기록 별 대여 금액 구하기

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

문제(일부):  요금테이블, 대여이력 테이블, 기간별 할인율 테이블을 사용하여 차종이 트럭인 대여기록별 대여금액 구하기

---

1. 서브쿼리 활용

1) 요금테이블과 대여이력 테이블을 JOIN

2) WHERE절에서 차종이 트럭인 것으로 필터링

3) 대여기간*요금*기간별할인율
3-1) 기간별 할인율은 서브쿼리를 사용
3-1-1) WHERE절에 CASE문을 활용하여 기간별 할인율 타입 사용, COALESCE로 할인율이 없는 경우 1을 사용

SELECT 
    H.HISTORY_ID,
    FLOOR(
        (DATEDIFF(H.END_DATE, H.START_DATE) + 1) * 
        C.DAILY_FEE * 
        COALESCE((SELECT (1 - P.DISCOUNT_RATE * 0.01)
                  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
                  WHERE P.CAR_TYPE = C.CAR_TYPE 
                    AND P.DURATION_TYPE = 
                        CASE 
                            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 90 THEN '90일 이상'
                            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 30 THEN '30일 이상'
                            WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 7 THEN '7일 이상'
                            ELSE NULL
                        END),1)) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
	JOIN CAR_RENTAL_COMPANY_CAR C USING(CAR_ID)
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC

2. WITH절 활용

1) WITH절로 대여기간 임시 테이블 생성

1-1) 대여이력과 요금테이블 JOIN하여 대여기간 조회

2) 1번과 동일한 방법으로 할인률 임시테이블 생성

3) 메인쿼리에서 할인률 임시테이블을 사용, 대여기간*요금*기간별할인율 구하기

WITH RentalPeriod AS (
    SELECT 
        H.HISTORY_ID, 
        C.CAR_TYPE, 
        DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS RENTAL_DAYS, 
        C.DAILY_FEE
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    JOIN 
        CAR_RENTAL_COMPANY_CAR C USING(CAR_ID)
    WHERE 
        C.CAR_TYPE = '트럭'),
DiscountedFees AS (
    SELECT 
        R.HISTORY_ID,
        R.DAILY_FEE,
        R.RENTAL_DAYS,
        COALESCE(
            (SELECT (1 - P.DISCOUNT_RATE * 0.01)
             FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
             WHERE P.CAR_TYPE = R.CAR_TYPE
            	AND P.DURATION_TYPE =
                    CASE
                        WHEN R.RENTAL_DAYS >= 90 THEN '90일 이상'
                        WHEN R.RENTAL_DAYS >= 30 THEN '30일 이상'
                        WHEN R.RENTAL_DAYS >= 7 THEN '7일 이상'
                    ELSE NULL
                    END), 1) AS ADJUSTED_FEE_FACTOR
    FROM RentalPeriod R)
SELECT 
    D.HISTORY_ID,
    FLOOR(D.RENTAL_DAYS * D.DAILY_FEE * D.ADJUSTED_FEE_FACTOR) AS FEE
FROM 
    DiscountedFees D
ORDER BY 
    FEE DESC, D.HISTORY_ID DESC;