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

💻 SQL 고득점 Kit > GROUP BY > 문제

✏️ 풀이

• 서브쿼리로 대여 시작일을 기준으로 대여 횟수가 5회 이상인 자동차 추출
• 자동차 비교 + 조건 기간 (재)추출

👉 제출 코드

MySQL

SELECT MONTH(START_DATE) MONTH, CAR_ID, COUNT(CAR_ID) 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(CAR_ID) >= 5)
    AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY CAR_ID, MONTH(START_DATE)
ORDER BY MONTH, CAR_ID DESC

Oracle

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

• TO_NUMBER를 사용하여 08이 아닌 8로 출력