8/21 每日一題(MYSQL 查詢一個月內活躍用戶的數量)

Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
This table may have duplicate rows.
The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website. 
Note that each session belongs to exactly one user.

 

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.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
Output: 
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
Explanation: Note that we do not care about days with zero active users.

# Write your MySQL query statement below

SELECT activity_date AS day, COUNT(DISTINCT user_id) as active_users

FROM Activity

WHERE activity_date BETWEEN '2019-06-28' and '2019-07-27'

GROUP BY activity_date --------------------------------------

這邊使用了GROUP BY activity_date 用日期進行分組,結果會按照每天的日期進行分組，使在指定日期範圍內有某一天沒有任何活動，也會在結果中有這個日期，並且 active_users 數量會是 0（零活躍用戶）。

如果沒有使用分組,那回傳的將會是日期範圍納總活躍用戶數,而不會區分每一天的活躍用戶數。換句話說，這個查詢結果只會給出一個總的活躍用戶數，而不會告訴你每一天有多少個活躍用戶。

 ------------------參考解答

select activity_date as day, count(distinct user_id) as active_users
from Activity 
where (activity_date > '2019-06-27' and activity_date <= '2019-07-27')
group by activity_date

----------------------------

# Write your MySQL query statement below

SELECT activity_date as day,count(distinct(user_id)) as active_users 

FROM Activity 

WHERE DATEDIFF('2019-07-27', activity_date) between 0 and 29  

GROUP BY activity_date