Given the Activity table, 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.
Activity Types: Any activity from ('open_session', 'end_session', 'scroll_down', 'send_message') will be considered valid activity for a user to be considered active on a day.
Return the result table in any order.
Table Schema
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | User identifier |
session_id
|
int | Session identifier |
activity_date
|
date | Date when activity occurred |
activity_type
|
enum | Type of activity: 'open_session', 'end_session', 'scroll_down', 'send_message' |
Input & Output
| 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 |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
| day | active_users |
|---|---|
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
For 2019-07-20: Users 1 and 2 were active (user 1 had multiple activities but counts as 1 unique user). For 2019-07-21: Users 2 and 3 were active. The activity on 2019-06-25 is outside the 30-day range so it's excluded.
| user_id | session_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2019-06-01 | open_session |
| 2 | 2 | 2019-08-01 | scroll_down |
| day | active_users |
|---|
No activities fall within the 30-day period ending 2019-07-27 (range: 2019-06-28 to 2019-07-27), so the result is empty.
Constraints
-
1 ≤ user_id ≤ 10^9 -
1 ≤ session_id ≤ 10^9 -
activity_dateis in valid date format -
activity_typeis one of('open_session', 'end_session', 'scroll_down', 'send_message')