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

문제

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

 

Pseudo Code

1. 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차를 구하는 SELECT문 출력

    - 2022년 8월부터 2022년 10월까지  -> START_DATE 날짜변환, between 연산 

    - 총 대여 횟수 5회 이상 -> COUNT

2. 1번을 where절에서 서브쿼리로 사용. 해당 서브쿼리에서 출력된 자동차 ID와 조건을 만족하는 메인쿼리 생성

    - SELECT * FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (서브 쿼리)

3.  월별 자동차 ID 별 이므로 그룹별 건수가 필요해 GROUP BY절 추가

    - 월별 -> EXTRACT 함수로 월만 추출

4. 특정 월의 총 대여 횟수가 0이면 제외해야 하므로 그룹별 집계된 결과 중 원하는 조건의 결과만 필터링 해줄 수 있는 HAVING절에서 걸러줌

5. 월 기준 오름차순 정렬, 월 같을 시 자동차 ID기준 내림차 순 정렬

 

내 코드

SELECT 
    EXTRACT(MONTH FROM START_DATE) MONTH,
    CAR_ID,
    COUNT(*) 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, 'YYYYMM') BETWEEN 202208 AND 202210
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
)
AND TO_CHAR(START_DATE, 'YYYYMM') BETWEEN 202208 AND 202210
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
HAVING COUNT(*) > 0
ORDER BY MONTH, CAR_ID DESC

 

 

서브 쿼리에 날짜 조건절을 걸어줬음에도 메인 쿼리에 한번더 작성한 이유

 

서브 쿼리의 결과는 자동차 ID별 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5번 이상인 자동차 ID들을 반환한다. 만일 메인쿼리에서 날짜 조건을 걸어 주지 않으면 메인쿼리 그룹 바이가 월에 걸려있기 때문에 년도는 걸러주지 않아 내가 원하는 결과를 얻을 수 없기 때문이다.