SQL Rolling Transaction Average | Dataford Interview Questions

WEX monitors transaction activity across its Fleet One accounts to identify changes in payment volume. Write a PostgreSQL query that calculates the rolling average of daily settled transaction volume for each WEX Fleet One account.

Requirements

Include only accounts whose product is WEX Fleet One and transactions with status = 'settled'.
Calculate daily transaction volume as the sum of amount, treating NULL amounts as zero.
Use a window function partitioned by account and ordered by transaction date to calculate the average daily volume across the current date and the preceding 29 calendar days.
Return one row for each account and date with observed settled activity, ordered by account and date.

Schema

accounts

Column	Type	Description
account_idPK	INTEGER	Unique account identifier
account_name	VARCHAR(100)	Business account name
product_name	VARCHAR(50)	WEX product associated with the account

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
account_id	INTEGER	Account associated with the transaction
transaction_date	DATE	Transaction settlement date
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction status
transaction_type	VARCHAR(30)	Transaction classification

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