대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 / 프로그래머스, SQL, MySQL

문제

source: 프로그래머

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: 
RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

 

 

 

 

제출답안(MySQL)

WITH 
    hist_aug_thru_oct
        AS 
            (SELECT 
                *, MONTH(start_date) AS month
            FROM 
                car_rental_company_rental_history
            WHERE
                MONTH(start_date) BETWEEN 8 AND 10),
                
    ls_car_over4_aug_thru_oct
        AS
            (SELECT car_id
            FROM hist_aug_thru_oct
            GROUP BY car_id
            HAVING COUNT(history_id) >= 5)


SELECT
    month, car_id, COUNT(history_id) AS record

FROM 
    hist_aug_thru_oct

WHERE
    car_id IN 
            (SELECT car_id
             FROM ls_car_over4_aug_thru_oct)

GROUP BY 
    month, car_id
    
ORDER BY
    month, car_id DESC

 

 

 

풀이(MySQL)

CTE1: 8월 ~ 10월에 대여된 차의 렌트 기록

WITH 
    hist_aug_thru_oct
        AS 
            (SELECT 
                *, MONTH(start_date) AS month
            FROM 
                car_rental_company_rental_history
            WHERE
                MONTH(start_date) BETWEEN 8 AND 10),

>> 예시를 보면 car 1은 2022-07-27 ~ 2022-08-02 렌트되었고 이는 8월달 렌트 기록에 카운트되지 않았다

>> 따라서 '대여' 여부 기준: start_date의 month를 기준으로 판단함

>> 이후 메인 SQL 문장에서 month를 사용할 수 있게 CTE에 추가해두었음

 

CTE 2: 8~10월 간 5회 이상(4회 초과) 렌트 된 차의 id 목

    ls_car_over4_aug_thru_oct
        AS
            (SELECT car_id
            FROM hist_aug_thru_oct
            GROUP BY car_id
            HAVING COUNT(history_id) >= 5)

>> CTE를 사용하지 않고 메인 구문에서 GROUP BY MONTH, car_id HAVING COUNT(history_id)>=5를 할 경우 매달 대여가 5회 된 이상 된 차의 데이터를 추출함

>> 따라서, 별도의 CTE를 통해 8~10월 전체에서 대여기간이 5회 이상인 차의 목록을 별도로 만들어 주었음

 

문제에서 요구한 대로 데이터를 추출

SELECT
    month, car_id, COUNT(history_id) AS record

FROM 
    hist_aug_thru_oct

WHERE
    car_id IN 
            (SELECT car_id
             FROM ls_car_over4_aug_thru_oct)

GROUP BY 
    month, car_id
    
ORDER BY
    month, car_id DESC

 

 

 

 

문제 바로가기(MySQL)