[SQL 문제 풀이] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

WITH FilteredData AS (
    -- 1. 2022년 8월부터 2022년 10월까지 데이터를 필터링
    SELECT 
        CAR_ID,
        MONTH(START_DATE) AS MONTH
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE 
        START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
),
QualifiedCars AS (
    -- 2. 총 대여 횟수가 5회 이상인 자동차 필터링
    SELECT 
        CAR_ID
    FROM 
        FilteredData
    GROUP BY 
        CAR_ID
    HAVING 
        COUNT(*) >= 5
),
MonthlyRecords AS (
    -- 3. 월별 대여 횟수 계산
    SELECT 
        MONTH(START_DATE) AS MONTH,
        CAR_ID,
        COUNT(*) AS RECORDS
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE 
        START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
        AND CAR_ID IN (SELECT CAR_ID FROM QualifiedCars)
    GROUP BY 
        MONTH(START_DATE), CAR_ID
)
-- 4. 결과 정렬
SELECT 
    MONTH,
    CAR_ID,
    RECORDS
FROM 
    MonthlyRecords
ORDER BY 
    MONTH ASC, -- 월 기준 오름차순
    CAR_ID DESC; -- 자동차 ID 기준 내림차순