How would you use SQL window functions to calculate a 7-day rolling average of customer usage by account for Spectrum?
Use the provided account and usage data. Calculate the average for each account and usage date using that date and the six preceding calendar days.
account_id, account_name, usage_date, daily_usage_gb, and rolling_7_day_avg_gb.account_id, then usage_date.| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Unique Spectrum customer account identifier |
| account_name | VARCHAR(100) | Customer account name |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INT | Unique usage record identifier |
| account_id | INT | Account associated with the usage record |
| usage_date | DATE | Date on which usage was recorded |
| usage_gb | DECIMAL(10,2) | Usage amount in gigabytes |