SQL: 7-Day Rolling Average | Dataford Interview Questions

CVS Health operations leaders need a daily view of sales trends across CVS Pharmacy locations. Write a PostgreSQL query that calculates each store's 7-day rolling average of daily sales for January 1 through January 7, 2024.

Requirements

Aggregate transaction amounts into one daily sales total per store and date.
Join the daily totals to the CVS store dimension to return the store code and name.
Calculate a calendar-based 7-day rolling average using a window function. Average only the dates with recorded sales, and round the result to two decimal places.
Return results ordered by store code and sale date.

Schema

stores

Column	Type	Description
store_idPK	INTEGER	Unique CVS store identifier
store_code	VARCHAR(10)	CVS store code
store_name	VARCHAR(100)	Store location name

store_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
store_id	INTEGER	References stores.store_id
sale_date	DATE	Date of the transaction
sale_amount	NUMERIC(12,2)	Sales amount for the transaction

Your question is SQL: 7-Day Rolling 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