7-Day Rolling Average Sales | Dataford Interview Questions

Walmart Labs wants category-level sales trends for Walmart's digital commerce reporting. Write a PostgreSQL query that calculates a seven-day rolling average of daily sales for each product category.

The rolling window must cover the current date and the six preceding calendar dates. Dates with no sales must count as zero, and categories with products but no sales during the period must still appear.

Requirements

Aggregate sales to one row per category and calendar date.
Generate dates from 2024-01-01 through 2024-01-07 and fill missing sales with zero.
Use a window function partitioned by category to calculate the rolling average.
Return results ordered by category and sales date.

Schema

products

Column	Type	Description
product_idPK	INTEGER	Unique product identifier
product_name	VARCHAR(100)	Product display name
category	VARCHAR(50)	Product category

sales

Column	Type	Description
sale_idPK	INTEGER	Unique sale identifier
product_id	INTEGER	Product sold
sale_date	DATE	Date on which the sale occurred
sales_amount	NUMERIC(12,2)	Monetary value of the sale

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