Rolling Average with Window Functions | Dataford Interview Questions

Write a query using SQL window functions to calculate a rolling average of sales over the last 30 days.

Use the sales and stores tables. Calculate the rolling average independently for each store, based on daily sales totals. Treat the current date as part of the 30-day period.

Output

One row per store and sales date with sales activity.
Columns: store_name, sale_date, daily_sales, and rolling_30_day_avg.
Include only stores matched to sales records, and order by store_name, then sale_date ascending.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store display name

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
store_id	INT	Store associated with the sale
sale_date	DATE	Date of the sale
amount	NUMERIC(12,2)	Sale amount

Your question is Rolling Average with Window Functions. Start with the requirements and the two tables 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