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

문제

MYSQL

내가 작성한 정답1

select month(start_date) month, car_id, count(history_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(history_id) >= 5
                )
    and start_date between '2022-08-01' and '2022-10-31'
group by 1,2
// group by를 할 때 특정 월의 count가 0이면 아예 해당 월이 결과에서 제외되므로 삭제해도 됨
// -> group by는 존재하는 데이터만 집계한다
having count(history_id) <> 0
order by 1 asc, 2 desc

내가 작성한 정답2 :

with r as (select month(start_date) month, car_id, count(history_id) records
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where start_date between '2022-08-01' and '2022-10-31'
            group by 1,2)
select month, car_id, records
from r
where car_id in (select car_id from r group by car_id having sum(records) >= 5)
order by 1 asc, 2 desc

ORACLE

내가 작성한 정답1

select extract(month from start_date) month, car_id, count(history_id) records
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where to_char(start_date,'yymmdd') between '220801' and '221031'
    and car_id in (select car_id
                  from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                  where to_char(start_date,'yymmdd') between '220801' and '221031'
                  group by car_id
                  having count(history_id) >= 5)
group by extract(month from start_date), car_id
order by month asc, car_id desc

내가 작성한 정답2 :

with a as (select extract(month from start_date) month, car_id, count(history_id) records
           from CAR_RENTAL_COMPANY_RENTAL_HISTORY
           where to_char(start_date,'yymmdd') between '220801' and '221031'
           group by extract(month from start_date), car_id)
select month, car_id, records
from a
where car_id in (select car_id from a group by car_id having sum(records) >= 5)
order by month asc, car_id desc