Rolling Window Customer Transactions | Dataford Interview Questions

Can you write an efficient SQL query to aggregate rolling windows of customer transaction data?

Assume the required window is 30 calendar days and only posted transactions should be included. Aggregate transactions by customer and transaction date before calculating the rolling total.

Output

One row per customer and transaction date with posted activity
Columns: customer_id, customer_name, transaction_date, daily_amount, and rolling_30_day_amount
Order by customer_id, then transaction_date ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
segment	VARCHAR(30)	Customer segment

transactions

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

Your question is Rolling Window Customer Transactions. 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