Rolling 7-Day Customer Average | Dataford Interview Questions

IDFC FIRST Bank wants to monitor short-term customer transaction patterns. Write a PostgreSQL query that calculates the rolling 7-day average transaction amount for each active customer using a window function.

Requirements

Include only active customers and transactions with transaction_status = 'POSTED'.
Exclude transactions where transaction_amount is NULL.
Calculate the average over the current transaction date and the preceding six calendar days, partitioned by customer.
Return one row per qualifying transaction, ordered by customer_id, transaction_date, and transaction_id.

Schema

bank_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name
is_active	BOOLEAN	Whether the customer has an active banking relationship

bank_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer who made the transaction
transaction_date	DATE	Transaction posting date
transaction_amount	NUMERIC(12,2)	Transaction amount
transaction_status	VARCHAR(20)	Transaction processing status

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