LeetCode MySQL-1141. User Activity for the Past 30 Days I

    AllenLi
    LeetCode MySQL

    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.

    寫一個 SQL Query來尋找截止到2019-07-27為期30天期間的每日活躍用戶數(每日至少進行一項活動，則處於活躍狀態)。
可任意排序結果。

    Table: Activity

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM 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.

    Solution:
    1. 選擇標題 activity_date 為 day。
    2. 選擇標題 不重複的user_id 為 active_users。
    3. 來自 Activity 的 table，且以 activity_date 的日期為群組。
    4. 有符合在 2019-07-27 ~ 2019-06-28 日期之間的 activity_date。

    Code1:

    SELECT 
	activity_date AS day,
	COUNT(DISTINCT user_id) AS active_users
    FROM Activity
GROUP BY activity_date
HAVING DATE (activity_date) BETWEEN '2019-06-28' AND '2019-07-27';

    newActivity

    Related Posts