Write Rolling Three-Month Averages SQL

MediumSQL · PostgreSQL

Problem

Commonwealth Bank of Australia wants to monitor monthly transaction activity for customers using its NetBank analytics data. Write a PostgreSQL query that calculates each eligible customer's monthly transaction total and rolling three-month average.

Requirements

Include active retail customers, including months with no qualifying transactions.
Aggregate only transactions with status = 'POSTED' from active accounts during January through April 2024.
Treat missing monthly activity and NULL amounts as zero.
Calculate the average of the current month and the two preceding calendar months for each customer. Round the result to two decimal places.
Return one row per customer per month, ordered by customer ID and month.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Customer identifier
customer_name	VARCHAR(100)	Customer name
customer_type	VARCHAR(20)	Retail or business classification
status	VARCHAR(20)	Customer status

accounts

Column	Type	Description
account_idPK	INTEGER	Account identifier
customer_id	INTEGER	Owning customer identifier
account_type	VARCHAR(30)	Account product type
status	VARCHAR(20)	Account status

transactions

Column	Type	Description
transaction_idPK	INTEGER	Transaction identifier
account_id	INTEGER	Related account identifier
transaction_date	DATE	Transaction date
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Tablescustomersaccountstransactions