SQL Rolling Average by City | Dataford Interview Questions

Write a SQL query using window functions to compute a 7-day rolling average of orders by city.

Use the orders table. Count only completed orders, exclude rows with a missing city, and treat dates without orders as zero-order days within each city's available date range.

Output

One row per city and calendar date in that city's range
Columns: city, order_date, daily_orders, and rolling_7_day_avg
Include the current date and previous six calendar days in the average, rounded to two decimal places
Sort by city, then order_date

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
city	VARCHAR(100)	City where the order was placed
order_date	DATE	Date when the order was placed
status	VARCHAR(30)	Order lifecycle status

Your question is SQL Rolling Average by City. 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