[SQL] 1141. User Activity for the Past 30 Days I

문제 링크
https://leetcode.com/problems/user-activity-for-the-past-30-days-i/

SELECT sub.day, COUNT(cnt) as active_users 
FROM (SELECT activity_date as day, COUNT(*) cnt
    FROM Activity
    WHERE DATEDIFF('2019-07-27', activity_date) <= 30
    GROUP BY user_id,activity_date) AS sub
GROUP BY 1
ORDER BY 1

내가 쓴 쿼리
WHERE DATEDIFF('2019-07-27', activity_date) <= 30

문제에서 요구하는 조건
Write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.

DATEDIFF('2019-06-17', '2019-06-17') 는 0로 출력.
통상적으로 30일 기간을 이야기 하면
당일 부터 (포함) 30일 이다.
쉽게 생각해서 1월 1일 기준으로 30일이라는 기간은 30일까지.

다시 내가 쓴 조건으로 돌아가서
WHERE DATEDIFF('2019-07-27', activity_date) <= 30
맨 뒤에 30을 붙이면 안되는 이유는
1월 1일 기준 30일은 30일 이니까
DATEDIFF ('2019-01-01','2019-01-30')까지 들어갈 수 있고, 이 차이는 30이 아니라 29다.

SELECT sub.day, COUNT(cnt) as active_users 
FROM (SELECT activity_date as day, COUNT(*) cnt
    FROM Activity
    WHERE DATEDIFF('2019-07-27', activity_date) <= 29
    GROUP BY user_id,activity_date) AS sub
GROUP BY 1
ORDER BY 1

30 > 29로 바꾸니 정답. 인 줄 알았으나
'2019-07-27' 이전 데이터인지까지 확인해야 정답.

SELECT sub.day, COUNT(cnt) as active_users 
FROM (SELECT activity_date as day, COUNT(*) cnt
    FROM Activity
    WHERE DATEDIFF('2019-07-27', activity_date) <= 29
    AND activity_date <= '2019-07-27'
    GROUP BY user_id,activity_date) AS sub
GROUP BY 1
ORDER BY 1