Your question is Window Functions Rolling Comparison. Start with the requirements and the one table 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 SQL query using window functions to compute a 7-day rolling average and compare it to the prior week for each user.
Assume user_daily_activity contains one row per user per calendar day. Return only dates where a prior-week comparison is available.
user_id, activity_date, rolling_7_day_avg, prior_week_rolling_avg, and difference_from_prior_weekuser_id, then activity_date| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record identifier |
| user_id | VARCHAR(50) | User associated with the daily activity |
| activity_date | DATE | Calendar date of the activity measurement |
| activity_value | DECIMAL(12,2) | Daily activity measure |