Rolling 7-Day Sales Average | Dataford Interview Questions

BASF wants to monitor daily sales trends for products tracked in its commercial sales system. Write a PostgreSQL query that calculates a rolling 7-day average of completed sales for each product.

Requirements

Aggregate completed transactions into one daily sales total per product, treating missing sales days as zero.
Generate a continuous calendar between each product's first and last completed sale.
Calculate the average across the current day and previous six calendar days using a window function.
Return product name, date, daily sales, and the rolling average, ordered by product and date.

Schema

products

Column	Type	Description
product_idPK	INTEGER	Unique product identifier
product_name	VARCHAR(100)	BASF product name
business_unit	VARCHAR(100)	Product business unit

sales

Column	Type	Description
sale_idPK	INTEGER	Unique sales transaction identifier
product_id	INTEGER	References products.product_id
sale_date	DATE	Date of the transaction
sale_amount	NUMERIC(12,2)	Transaction sales value
status	VARCHAR(20)	Transaction status

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