[프로그래머스/SQL] Level 3_대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(GROUP BY)

📌문제 유형

GROUP BY

 

📌문제

프로그래머스

 

📌나의 문제풀이

-- WHERE에 다시 조건을 걸어줘야 전체 TABLE에 걸림 
-- 서브쿼리에서 추출한 RECORD를 전체 TABLE에서 MONTH,CAR_ID 기준으로 나눔
SELECT MONTH(START_DATE) AS MONTH, A.CAR_ID, COUNT(A.RECORD) AS RECORDS
FROM (SELECT CAR_ID, COUNT(*) AS RECORD  
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
      WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31' 
      GROUP BY CAR_ID 
      HAVING COUNT(*) >= 5) AS A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON A.CAR_ID = H.CAR_ID
WHERE H.START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH(START_DATE), H.CAR_ID
ORDER BY MONTH(START_DATE), A.CAR_ID DESC

 

📌다른사람의 문제풀이

- WHERE절에서 날짜조건 한 번 더 주는 이유

• FROM 절에 있는 `CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블에서 한 번 더 걸러줘야하기 때문

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(RECORD) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS A
JOIN (SELECT CAR_ID, COUNT(*) AS RECORD    
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
      WHERE START_DATE >= '2022-08-01' AND START_DATE <= '2022-10-31'
      GROUP BY CAR_ID
      HAVING RECORD >= 5
) AS B USING (CAR_ID)
WHERE A.START_DATE >= '2022-08-01' AND A.START_DATE <= '2022-10-31' 
GROUP BY MONTH(START_DATE), CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;