Rolling 7-Day Average by Region | Dataford Interview Questions

Write a SQL query to calculate the rolling 7-day average of product sales across different regions.

Use the product and sales data provided. Treat dates with no sales as zero sales, and calculate the average for each product and region over the current date and six preceding calendar dates.

Output

One row per product, region, and calendar date in the sales date range.
Columns: product_name, region, sales_date, daily_sales, and rolling_7_day_avg.
Include dates with no sales and return results ordered by product_name, region, and sales_date ascending.

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Product name
product_category	VARCHAR(100)	Product category

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
product_id	INT	Product sold
region	VARCHAR(80)	Sales region
sale_date	DATE	Date of the sale
quantity	INT	Units sold

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