Your question is Running Moving Average per System. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a query using SQL window functions to calculate a running 30-day moving average of user activity per system.
Use activity_logs to calculate daily activity as the total of activity_units. Treat NULL activity units as zero. The 30-day period includes the current activity date and the preceding 29 calendar days.
system_name, activity_date, daily_activity, and moving_30_day_avg.system_name, then activity_date ascending.| Column | Type | Description |
|---|---|---|
| system_idPK | INT | Unique system identifier |
| system_name | VARCHAR(100) | System display name |
| classification | VARCHAR(30) | System classification |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record identifier |
| user_id | INT | Identifier of the user generating activity |
| system_id | INT | System associated with the activity |
| activity_date | DATE | Calendar date of the activity |
| activity_units | INT | Number of activity units recorded |