7-Day Rolling Average Transactions | Dataford Interview Questions

Chase fraud and customer analytics teams need a daily activity baseline for customers using Chase Checking. Write a PostgreSQL query that calculates each customer's 7-day rolling average of posted Checking transactions.

Requirements

Join customers to transactions and include only active Chase customers with account_type = 'CHECKING'.
Treat a NULL transaction status as POSTED, exclude explicitly reversed transactions, and aggregate transactions by customer and calendar date.
Fill dates with no transactions as zero-count days between each customer's first and last qualifying transaction.
Use a window function to calculate the average of the current date and up to the six preceding calendar dates.
Return customer_id, customer_name, transaction_date, daily_transaction_count, and rolling_avg_7d, rounded to two decimals. Order by customer and date.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Chase customer identifier
customer_name	VARCHAR(100)	Customer display name
is_active	BOOLEAN	Whether the customer is currently active

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Date on which the transaction posted
account_type	VARCHAR(30)	Account category for the transaction
status	VARCHAR(30)	Transaction processing status

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