7-Day Rolling Revenue with Window Functions | Dataford Interview Questions

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.

Output

One row per customer and revenue date with columns customer_id, revenue_date, daily_revenue, and rolling_7_day_average.
The average must cover the current date and preceding six calendar days for that customer.
Order by customer_id, then revenue_date.

Schema

revenue_events

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

Your question is 7-Day Rolling Revenue with Window Functions. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem