Contents
내가 작성한 쿼리
내가 작성한 쿼리
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 기준 내림차순
Share article