7-Day Rolling Average With SQL | Dataford Interview Questions

MSCI Analytics Platform monitors transaction activity to support customer-level financial analysis. Write a PostgreSQL query that calculates a 7-day rolling average transaction amount for every transaction.

Requirements

Join transactions to customers to include the customer name when available.
Partition calculations by customer_id.
For each transaction, average amounts from the current transaction date and the preceding six calendar days. NULL amounts should be ignored by the average.
Return results ordered by customer, transaction date, and transaction ID. Preserve transactions whose customer record is missing.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's display name

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	Customer associated with the transaction
transaction_date	DATE	Calendar date of the transaction
amount	NUMERIC(12,2)	Transaction amount

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