Rolling Average Transactions SQL | Dataford Interview Questions

Your question is Rolling Average Transactions SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Airtel Payments Bank wants to monitor short-term transaction activity for active users. Write a PostgreSQL query that calculates a 7-day rolling average of each user's daily transaction count.

The rolling window should include the current transaction date and the preceding six calendar days. Average only the dates present in the daily transaction data, rather than treating missing dates as zero-transaction days.

Requirements

Include only users whose account_status is active.
Aggregate multiple transactions for the same user and date into one daily_transactions value.
Use a window function partitioned by user and ordered by transaction date.
Return user_id, full_name, transaction_date, daily_transactions, and rolling_7_day_average, ordered by user and date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Airtel Payments Bank user identifier
full_name	VARCHAR(100)	User's full name
account_status	VARCHAR(20)	Current account status

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
user_id	INTEGER	User associated with the transaction
transaction_date	DATE	Calendar date of the transaction
amount	NUMERIC(12,2)	Transaction amount