SQL 7-Day Rolling Average Sales | Dataford Interview Questions

Digitas México needs a daily performance view for products promoted through its digital campaigns. Write a PostgreSQL query that calculates each product's seven-day rolling average of daily completed sales.

Requirements

Aggregate sales by product_name and sale_date.
Count only rows where sale_status = 'completed'; treat dates with no completed sales as zero.
Calculate the average of the current day and up to the six preceding daily rows for each product.
Return results ordered by product and date, with the rolling average rounded to two decimal places.

Schema

campaign_sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
product_name	VARCHAR(100)	Product associated with the campaign
sale_date	DATE	Date of the sale
sale_amount	NUMERIC(10,2)	Sale amount
sale_status	VARCHAR(20)	Sale processing status

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

Requirements

Aggregate sales by product_name and sale_date.
Count only rows where sale_status = 'completed'; treat dates with no completed sales as zero.
Calculate the average of the current day and up to the six preceding daily rows for each product.
Return results ordered by product and date, with the rolling average rounded to two decimal places.

Schema

campaign_sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
product_name	VARCHAR(100)	Product associated with the campaign
sale_date	DATE	Date of the sale
sale_amount	NUMERIC(10,2)	Sale amount
sale_status	VARCHAR(20)	Sale processing status

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