Task
Facebook wants to monitor short-term engagement trends by tracking the 7-day rolling average of daily active users (DAU). Write a SQL query to calculate this metric from app activity logs.
Requirements
- Treat a user as active on a day if they generated at least one event on that date.
- Compute daily active users as the count of distinct users per activity date.
- Return the 7-day rolling average of DAU using the current date and previous 6 dates in the result set.
- Output
activity_date, daily_active_users, and rolling_7d_avg_dau.
- Order the final result by
activity_date ascending.
Table Definitions
facebook_activity
| Column | Type | Description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | Facebook user who generated the event |
| event_time | TIMESTAMP | Timestamp of the activity event |
| event_name | VARCHAR(50) | Type of activity event |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
facebook_activity
| event_id | user_id | event_time | event_name | device_type |
|---|
| 5 | 104 | 2024-01-02 08:00:00 | like | Android |
| 1 | 101 | 2024-01-01 09:15:00 | login | iPhone |
| 9 | 106 | 2024-01-03 11:00:00 | comment | Web |
| 3 | 102 | 2024-01-01 12:30:00 | view_story | Android |
| 12 | 101 | 2024-01-04 07:45:00 | login | iPhone |
| 16 | 108 | 2024-01-05 13:00:00 | like | Android |
| 20 | 109 | 2024-01-06 18:20:00 | view_story | Web |
| 24 | 110 | 2024-01-07 09:00:00 | login | iPhone |
| 28 | 111 | 2024-01-08 14:10:00 | comment | Android |
| 31 | 112 | 2024-01-09 10:30:00 | login | Web |
Expected Output
| activity_date | daily_active_users | rolling_7d_avg_dau |
|---|
| 2024-01-01 | 2 | 2.00 |
| 2024-01-02 | 3 | 2.50 |
| 2024-01-03 | 3 | 2.67 |
| 2024-01-04 | 3 | 2.75 |
| 2024-01-05 | 3 | 2.80 |
| 2024-01-06 | 2 | 2.67 |
| 2024-01-07 | 3 | 2.71 |
| 2024-01-08 | 3 | 2.86 |
| 2024-01-09 | 2 | 2.71 |
