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

문제

MYSQL

내가 작성한 정답

WITH A AS (SELECT HISTORY_ID,CAR_ID,START_DATE, MONTH(START_DATE) MONTH
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE DATE_FORMAT(START_DATE,'%y%m') BETWEEN '2208' AND '2210')
SELECT MONTH, CAR_ID, COUNT(CAR_ID) RECORDS
FROM A 
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING COUNT(HISTORY_ID)>=5)
GROUP BY MONTH, CAR_ID 
ORDER BY 1, 2 DESC;

ORACLE

내가 작성한 오답

WITH A AS(SELECT CAR_ID, START_DATE, HISTORY_ID, TO_CHAR(START_DATE,'MM') MONTH
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE TO_CHAR(START_DATE,'YYMM') BETWEEN '2208' AND '2210')
SELECT MONTH, CAR_ID, COUNT(CAR_ID) RECORDS
FROM A
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING COUNT(HISTORY_ID)>=5)
GROUP BY MONTH, CAR_ID
ORDER BY 1, 2 DESC;

내가 작성한 정답

#1 문자로 변환된 월을 숫자로 변환
WITH A AS(SELECT CAR_ID, START_DATE, HISTORY_ID, TO_CHAR(START_DATE,'MM')+0 MONTH
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE TO_CHAR(START_DATE,'YYMM') BETWEEN '2208' AND '2210')
SELECT MONTH, CAR_ID, COUNT(CAR_ID) RECORDS
FROM A
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING COUNT(HISTORY_ID)>=5)
GROUP BY MONTH, CAR_ID
ORDER BY 1, 2 DESC;

#2 날짜에서 월만 추출  EXTRACT(MONTH FROM COLUMN)
WITH A AS(SELECT CAR_ID, START_DATE, HISTORY_ID, EXTRACT(MONTH FROM START_DATE) MONTH--TO_CHAR(START_DATE,'MM') MONTH
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE TO_CHAR(START_DATE,'YYMM') BETWEEN '2208' AND '2210')
SELECT MONTH, CAR_ID, COUNT(CAR_ID) RECORDS
FROM A
WHERE CAR_ID IN (SELECT CAR_ID FROM A GROUP BY CAR_ID HAVING COUNT(HISTORY_ID)>=5)
GROUP BY MONTH, CAR_ID
ORDER BY 1, 2 DESC;