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

문제

MYSQL

내가 작성한 정답

WITH A AS (
    SELECT CAR_ID, EXTRACT(MONTH FROM START_DATE) MONTH ,COUNT(HISTORY_ID) RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-07-31' < START_DATE AND START_DATE < '2022-11-01'
    GROUP BY CAR_ID,EXTRACT(MONTH FROM START_DATE)
)
SELECT MONTH, CAR_ID, RECORDS
FROM A
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING SUM(RECORDS)>=5)
			AND RECORDS != 0
ORDER BY MONTH ASC, CAR_ID DESC

다른 사람들이 작성한 정답

SELECT 
MONTH(START_DATE) AS MONTH,
CAR_ID,
COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE (START_DATE >= '2022-08-01' AND START_DATE <= '2022-11-01')
AND CAR_ID IN (SELECT 
                 CAR_ID
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 

                 WHERE START_DATE >= '2022-08-01'
                   AND START_DATE <= '2022-11-01'

                GROUP BY CAR_ID
                HAVING COUNT(*)>=5)
GROUP BY MONTH,CAR_ID

ORDER BY MONTH, CAR_ID DESC;

ORACLE

내가 작성한 정답

WITH A AS (
    SELECT CAR_ID, EXTRACT(MONTH FROM START_DATE) MONTH , COUNT(HISTORY_ID) RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '220731'< TO_CHAR(START_DATE,'YYMMDD') AND TO_CHAR(START_DATE,'YYMMDD') < '221101'
    GROUP BY CAR_ID,EXTRACT(MONTH FROM START_DATE))
SELECT MONTH, CAR_ID, RECORDS
FROM A 
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING SUM(RECORDS) >=5)
        AND RECORDS != 0
ORDER BY MONTH ASC, CAR_ID DESC