SQL Window Functions Rolling Average | Dataford Interview Questions

Your question is SQL Window Functions Rolling Average. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

AArete analysts need a smoothed view of regional sales performance. Write a PostgreSQL query that calculates a rolling average of daily completed sales for each region.

Requirements

Join sales to stores to obtain the region.
Include only completed sales from January 1 through January 4, 2024, then aggregate sales by region and sale date.
Calculate the average of the current day and the two preceding recorded sales days within each region. Round the result to two decimal places.
Return all regional dates, including dates whose completed sales amount is NULL, and order by region and date.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
region	VARCHAR(50)	Operating region

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
store_id	INT	Store associated with the sale
sale_date	DATE	Date on which the sale occurred
amount	DECIMAL(10,2)	Sale amount, possibly NULL
status	VARCHAR(20)	Transaction status