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

[hwangJi-dev]

💬 문제

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

💬 풀이

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10' AND 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(CAR_ID) >= 5)
                                                        
GROUP BY CAR_ID, MONTH(START_DATE)
HAVING RECORDS >= 1
ORDER BY MONTH, CAR_ID DESC

💬 알게된 문법

월별 통계

• MONTH(##)

주별 통계

SELECT DATE_FORMAT(regist_date, '%Y-%m') AS WEEKEND,
			 DATE_FORMAT(DATE_SUB('regist_date', INTERVAL (DAYOFWEEK(regist_date)-1) DAY), '%Y/%m/%d') AS Week_Start,
			 DATE_FORMAT(DATE_SUB('regist_date', INTERVAL (DAYOFWEEK(regist_date)-7) DAY), '%Y/%m/%d') AS Week_END,
			 SUM(value) AS value
FROM TEST
GROUP BY WEEKEND

일별 통계

SELECT DATE(regist_date) AS DATE,
			 SUM(value) AS value
FROM TEST
GROUP BY DATE