7-Day Rolling Average Orders | Dataford Interview Questions

Write a SQL query to calculate a 7-day rolling average of daily orders by store using window functions at Target.

Use the provided stores and orders tables. Include calendar dates with no orders so they contribute zero to the daily count. Treat the rolling average as the average of the current date and up to the six preceding calendar dates.

Output

One row per store and calendar date.
Columns: store_id, store_name, order_date, daily_orders, and rolling_7_day_avg.
Include all stores and dates in the order data's overall date range.
Sort by store_id, then order_date.

Schema

stores

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
store_id	INT	Store where the order was placed
customer_id	VARCHAR(30)	Customer identifier associated with the order
order_date	DATE	Calendar date when the order was placed

Your question is 7-Day Rolling Average Orders. 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