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

silver

Jun 09, 2025

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

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

내가 작성한 오답

: TO_CHAR로 문자열로 변환시켜서 month가 숫자가 아닌 문자로 인식 되어 오답처리 되었다


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;