Rolling 7-Day Sales with SQL | Dataford Interview Questions

How would you use SQL window functions to calculate rolling 7-day sales for each restaurant location?

Use the provided restaurant and sales data. Calculate results only for locations with recorded sales, and treat the rolling period as the current sale date plus the six preceding calendar days.

Output

One row per restaurant location and date with recorded sales.
Columns: restaurant_id, location_name, sale_date, daily_sales, and rolling_7_day_sales.
Order by restaurant_id, then sale_date ascending.

Output details

Sum all sales amounts for each restaurant location and sale_date to produce daily_sales.
Include dates having sales records even when every sales_amount is NULL.
Treat NULL amounts as ignored by sums, with daily_sales and rolling_7_day_sales remaining NULL when no non-NULL amount exists.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique identifier for a restaurant location
brand	VARCHAR(50)	Yum! Brands restaurant brand
location_name	VARCHAR(100)	Display name for the restaurant location

sales

Column	Type	Description
sale_idPK	INT	Unique identifier for a sales transaction
restaurant_id	INT	Restaurant location associated with the sale
sale_date	DATE	Calendar date of the sale
sales_amount	DECIMAL(12,2)	Sales amount recorded for the transaction

Your question is Rolling 7-Day Sales with SQL. 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