Task
StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.
A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.
Requirements
- Count distinct active users per day from the
user_events table.
- Calculate the 7-day rolling average of daily active users, ordered by event date.
- Include dates even when
user_id is NULL; those rows should not count toward active users.
- Return the columns
event_date, daily_active_users, and rolling_7_day_avg.
- Order the final result by
event_date ascending.
Table Definitions
user_events
| column | type | description |
|---|
| event_id | INT | Unique event record ID |
| user_id | INT | User who generated the event; can be NULL |
| event_name | VARCHAR(50) | Type of event |
| event_time | TIMESTAMP | Timestamp of the event |
Sample Data
user_events
| event_id | user_id | event_name | event_time |
|---|
| 5 | 103 | login | 2024-03-03 08:00:00 |
| 1 | 101 | login | 2024-03-01 09:00:00 |
| 11 | 105 | login | 2024-03-05 12:00:00 |
| 3 | 101 | purchase | 2024-03-02 11:00:00 |
| 8 | 104 | login | 2024-03-04 10:30:00 |
| 14 | NULL | app_open | 2024-03-06 08:00:00 |
| 18 | 108 | login | 2024-03-08 14:00:00 |
| 20 | 109 | login | 2024-03-09 09:00:00 |
Expected Output
| event_date | daily_active_users | rolling_7_day_avg |
|---|
| 2024-03-01 | 2 | 2.0000 |
| 2024-03-02 | 2 | 2.0000 |
| 2024-03-03 | 2 | 2.0000 |
| 2024-03-04 | 3 | 2.2500 |
| 2024-03-05 | 2 | 2.2000 |
| 2024-03-06 | 1 | 2.0000 |
| 2024-03-07 | 2 | 2.0000 |
| 2024-03-08 | 2 | 2.0000 |
| 2024-03-09 | 2 | 2.0000 |
