[다시보기]GROUP BY - 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

 

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

프로그래머스

 

 

문제 

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 

 

자동차 들에 대해서 ==> 서브 쿼리로 그 해당 자동차 id걸러낸 뒤 !

 

 

 

 

select Month(START_DATE) as MONTH,CAR_ID,COUNT(*) as RECORD
from CAR_RENTAL_COMPANY_RENTAL_HISTORY

where CAR_ID in 
(SELECT CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where DATE_FORMAT(START_DATE,'%Y-%m') between '2022-08' and '2022-10'
group by CAR_ID	
having COUNT(*)>=5)
and DATE_FORMAT(START_DATE,'%Y-%m') between '2022-08' and '2022-10'

group by MONTH,CAR_ID
having count(*)>0
order by MONTH,CAR_ID DESC