Rolling 30-Day Transaction Average | Dataford Interview Questions

Scotiabank's customer analytics team wants to monitor recent transaction behaviour. Write a PostgreSQL query that calculates a rolling 30-day average transaction amount for each active customer.

Use the current transaction date plus the preceding 29 calendar days, and calculate the average across non-null transactions in that window.

Requirements

Join transactions to customers and include only active customers.
Partition the window calculation by customer and order it by transaction date.
Return each transaction with the customer's ID, name, date, amount, and rolling 30-day average, rounded to two decimal places.
Sort the final results by customer ID and transaction date.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name
is_active	BOOLEAN	Whether the customer is currently active

transactions

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

Your question is Rolling 30-Day Transaction 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