[leetcode] 1141. User Activity for the Past 30 Days I / DATE_ADD, DATEDIFF

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

User Activity for the Past 30 Days I - LeetCode

 

Write an SQL query 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.

Return the result table in any order.

 

 

정답

# 1
SELECT activity_date day
    , COUNT(DISTINCT user_id) active_users
FROM Activity
WHERE activity_date BETWEEN DATE_ADD('2019-07-27', INTERVAL -29 DAY) 
    AND '2019-07-27'
GROUP BY activity_date;

# 2
SELECT activity_date AS day
    , COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date)<30 AND activity_date <= '2019-07-27'
GROUP BY activity_date;