자동차 대여 기록 별 대여 금액 구하기

Link

Tables

erDiagram
	CAR_RENTAL_COMPANY_CAR {
		CAR_ID INTEGER PK
		CAR_TYPE VARCHAR "세단, SUV, 승합차, 트럭, 리무진"
		DAILY_FEE INTEGER
		OPTIONS VARCHAR
	}
	CAR_RENTAL_COMPANY_RENTAL_HISTORY {
		HISTORY_ID INTEGER PK
		CAR_ID INTEGER FK
		START_DATE DATE
		END_DATE DATE
	}
	CAR_RENTAL_COMPANY_DISCOUNT_PLAN {
		PLAN_ID INTEGER PK
		CAR_TYPE VARCHAR
		DURATION_TYPE VARCHAR
		DISCOUNT_RATE INTEGER
	}
	CAR_RENTAL_COMPANY_CAR ||--o{ CAR_RENTAL_COMPANY_RENTAL_HISTORY : Rent_History
	CAR_RENTAL_COMPANY_CAR ||--|{ CAR_RENTAL_COMPANY_DISCOUNT_PLAN : Discount_Plan
Mermaid
복사

Question

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

DataFlow

•

대여 날짜에 따라서 달라지는 discount plan 적용

•

diff_date 사용 + 1

•

할인후 총액 정수 출력

•

7일 미만 할인률 0

Answer

WITH t_his AS (
    SELECT
        h.*,
        t.CAR_TYPE,
        t.DAILY_FEE,
        DATEDIFF(h.END_DATE, h.START_DATE) + 1 AS days,
        CASE
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 7 THEN "7일 미만"
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 30 THEN "7일 이상"
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 90 THEN "30일 이상"
            ELSE "90일 이상"
        END AS dur_days
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
    JOIN (
        SELECT
            *
        FROM CAR_RENTAL_COMPANY_CAR
        WHERE CAR_TYPE = "트럭"
    ) t
        ON h.CAR_ID = t.CAR_ID
),
result AS (
    SELECT
        h.*,
        CASE WHEN p.DURATION_TYPE IS NULL THEN "7일 미만" ELSE p.DURATION_TYPE END AS DURATION_TYPE,
        CASE WHEN p.DISCOUNT_RATE IS NULL THEN 0 ELSE p.DISCOUNT_RATE * 0.01 END AS DISCOUNT_RATE
    FROM t_his h
    LEFT JOIN (
        SELECT *
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = "트럭"
    ) p
        ON h.CAR_TYPE = p.CAR_TYPE
        AND p.DURATION_TYPE = h.dur_days
)

SELECT
    HISTORY_ID,
    TRUNCATE((DAILY_FEE * (1-DISCOUNT_RATE)) * days, 0) AS FEE
FROM result
ORDER BY FEE DESC, HISTORY_ID DESC;
SQL
복사