SQL Rolling Purchase Average | Dataford Interview Questions

Write a SQL query using window functions to calculate each customer's 7-day rolling average purchase amount.

Assume the source table is purchases. Include each purchase in the result, and calculate the average across that purchase date and the six preceding calendar days. NULL purchase amounts should not contribute to the average.

Output

One row per purchase with customer_id, purchase_date, purchase_amount, and rolling_7_day_avg
Include purchases whose amount is NULL, with a NULL average when no non-NULL amount exists in the window
Order by customer_id, purchase_date, and purchase_id

Schema

purchases

Column	Type	Description
purchase_idPK	INT	Unique identifier for the purchase
customer_id	INT	Identifier of the customer who made the purchase
purchase_date	TIMESTAMP	Timestamp when the purchase occurred
purchase_amount	DECIMAL(10,2)	Purchase amount, which may be NULL

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

Write a SQL query using window functions to calculate each customer's 7-day rolling average purchase amount.

Output

One row per purchase with customer_id, purchase_date, purchase_amount, and rolling_7_day_avg
Include purchases whose amount is NULL, with a NULL average when no non-NULL amount exists in the window
Order by customer_id, purchase_date, and purchase_id

Schema

purchases

Column	Type	Description
purchase_idPK	INT	Unique identifier for the purchase
customer_id	INT	Identifier of the customer who made the purchase
purchase_date	TIMESTAMP	Timestamp when the purchase occurred
purchase_amount	DECIMAL(10,2)	Purchase amount, which may be NULL

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