Description
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_typecolumn 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.
Problem Statement
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:
Activitytable:
| 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
The problem is asking for finding number of active users in the last 30 days. The number of users can be found by user_id column. Basically, you need to find number of distinct user_id per day for the last 30 days. You can filter records for the last 30 days using WHERE activity_date > DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date <= '2019-07-27' clause.
1SELECT activity_date day, COUNT(DISTINCT user_id) active_users
2 FROM Activity
3 WHERE activity_date > DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date <= '2019-07-27'
4 GROUP BY day
Comments