Rolling 7-Day Transaction Average | Dataford Interview Questions

Truist analysts need to monitor short-term customer transaction activity across accounts. Write a PostgreSQL query that calculates a rolling 7-day average of each customer's daily transaction total.

Requirements

Aggregate transactions by customer and transaction date before applying the window function.
Calculate a calendar-based seven-day average using the current date and the preceding six days.
Ignore customers without transactions and ignore NULL transaction amounts in aggregate calculations.
Return results ordered by customer and transaction date.

Schema

customers

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

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	References customers.customer_id
transaction_date	DATE	Transaction posting date
amount	NUMERIC(12,2)	Transaction amount

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