Oracle / 프로그래머스 - 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

 

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH,
       CAR_ID,
       COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= DATE '2022-08-01'
  AND START_DATE <  DATE '2022-11-01'
  AND CAR_ID IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE START_DATE >= DATE '2022-08-01'
          AND START_DATE <  DATE '2022-11-01'
        GROUP BY CAR_ID
        HAVING COUNT(*) >= 5
  )
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;