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

반응형

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/151139

프로그래머스

 

 

정답 쿼리

WITH FILTERED_HISTORY AS (
    SELECT *
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
),
QUALIFIED_CARS AS (
    SELECT CAR_ID
    FROM FILTERED_HISTORY
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)

SELECT MONTH(START_DATE) AS MONTH,
       CAR_ID,
       COUNT(*) AS RECORDS
FROM FILTERED_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID FROM QUALIFIED_CARS)
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC;

 

노트

복잡한 쿼리문 짤때는 WITH문으로 나눠서 생각하기!