Your question is 7-Day Rolling Revenue with Window Functions. 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 calculate a 7-day rolling average of revenue by customer.
Use revenue_events as the source table. Combine multiple events for the same customer and date into one daily total, and exclude rows with a null customer_id.
customer_id, revenue_date, daily_revenue, and rolling_7_day_average.customer_id, then revenue_date.| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique revenue event identifier |
| customer_id | INT | Customer associated with the revenue event |
| revenue_date | DATE | Calendar date on which revenue was recorded |
| revenue | NUMERIC(12,2) | Revenue amount for the event |