[MySQL] 코드카타 69번 DOUBLE GROUP BY

코딩테스트 연습 - 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

프로그래머스

 

[자료 설명]

[문제]

 

[나의 답변]

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE CAR_ID IN (SELECT CAR_ID 
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                     GROUP BY CAR_ID
                     HAVING COUNT(*) >= 5)
            AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
    ORDER BY MONTH, CAR_ID DESC

 

[WITH 를 이용한 참고 답변]

-- 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차 구하기 
WITH CTE AS (
    SELECT CAR_ID, SUM(YEAR(START_DATE) = 2022 AND MONTH(START_DATE) IN (8,9,10)) RESULT
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID 
    HAVING RESULT >= 5 
)
SELECT 
MONTH(START_DATE) MONTH,	
CAR_ID,	
COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (CAR_ID IN (SELECT CAR_ID 
                 FROM CTE))
      AND MONTH(START_DATE) BETWEEN 8 AND 10            
GROUP BY 1, 2
ORDER BY 1, 2 DESC;