특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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 PK
		DURATION_TYPE VARCHAR
		DISCOUNT_RATE INTEGER
	}
	CAR_RENTAL_COMPANY_CAR ||--o{ CAR_RENTAL_COMPANY_RENTAL_HISTORY : HISTORY
	CAR_RENTAL_COMPANY_DISCOUNT_PLAN ||--|| CAR_RENTAL_COMPANY_CAR : PLAN
Mermaid
복사

Question

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

DataFlow

•

세단, SUV

•

2022년 11월 1일 ~ 11월 30일까지 대여가능 - 30일 -> END_DATE 만 MAX면 가능

•

FEE: 50만원 이상 200만원 미만

Answer

WITH his AS (
    SELECT c.*
    FROM CAR_RENTAL_COMPANY_CAR c
    JOIN (
        SELECT
            CAR_ID,
            MAX(END_DATE) AS END_DATE
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        GROUP BY CAR_ID
        HAVING END_DATE < "2022-11-01 00:00:00"
    ) h
        ON c.CAR_ID = h.CAR_ID
        AND c.CAR_TYPE IN ("세단", "SUV")
),
result AS (
    SELECT
        h.*,
        p.DISCOUNT_RATE * 0.01 AS DISCOUNT_RATE
    FROM his h
    JOIN (
        SELECT *
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE DURATION_TYPE = "30일 이상"
    ) p
        ON h.CAR_TYPE = p.CAR_TYPE
)
SELECT
    CAR_ID,
    CAR_TYPE,
    ROUND((daily_fee * 30) * (1 - discount_rate), 0) AS FEE
FROM result
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;
SQL
복사