SQL分群語法 列出活躍使用者 Leetcode #1141

Peter Conlan on Unsplash

題目敘述

題目會給我們一張Activity資料表，裡面分別有user_id、 session_id、activity_date 、activity_type等欄位。

要求我們，以日期做分群，列出過去30天，對於每一天，相對應的活躍使用者的數目。

活躍使用者的定義為2019-07-27包含這天，往前三十天的區間內，至少有過一次活動紀錄的使用者。

輸出的順序不拘。

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.

詳細的題目可在這裡看到

測試範例

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.
七月20日，user 1 和 user 2 為活躍使用者
七月21日​，user 2 和 user 3 為活躍使用者

約束條件

活躍使用者的定義為2019-07-27包含這天，往前三十天的區間內，至少有過一次活動紀錄的使用者。

演算法

有點變化的進階題，在基本的查詢語法上，加上分群GROUP BY語法來對搜尋結果做分類。

如果是第一次接觸SQL的同學，請到這邊學習基本的SQL 語法。

SELECT ...欄位 FROM ...表格 WHERE ...條件

GROUP BY ...分群的依據欄位

實作上有個小細節要留意，因為有包含尾巴2019-07-27這天，所以往前數30天，區間的第一天是2019-06-28。

程式碼

SELECT  activity_date AS day, COUNT(DISTINCT user_id) AS active_users 
FROM Activity
# Filter those activities on latest 30 days
WHERE activity_date BETWEEN "2019-06-28" AND "2019-07-27"
GROUP BY activity_date;

關鍵知識點

掌握SELECT ...欄位 FROM ...表格 WHERE ...條件

GROUP BY ...分群的語法即可。

Reference:

[1] MySQL with GROUP BY ... syntax - User Activity for the Past 30 Days I - LeetCode