Rolling Moving Averages SQL | Dataford Interview Questions

Write a query using SQL window functions to calculate rolling three-month moving averages of client transactions.

Use the clients and transactions tables. Calculate monthly transaction totals for each client, then calculate the average of the current month and available transaction totals from the preceding two calendar months.

Output

One row per client and month represented in the transaction data
Columns: client_id, client_name, month_start, monthly_transaction_total, and rolling_three_month_avg
Sort by client_id, then month_start ascending

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(150)	Client display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
client_id	INT	References clients.client_id
transaction_date	DATE	Date the transaction occurred
amount	DECIMAL(12,2)	Transaction amount

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