Running 3-Month Moving Average | Dataford Interview Questions

Bain & Company's analytics team wants a monthly view of revenue performance by sales region. Write a PostgreSQL query that aggregates completed sales and calculates a running three-month moving average for each region.

Requirements

Aggregate completed transaction revenue by region and calendar month.
Calculate the average of the current month and up to the two preceding reported months within each region.
Exclude cancelled transactions and rows without a matching region.
Return results ordered by region and month.

Schema

regions

Column	Type	Description
region_idPK	INTEGER	Unique region identifier
region_name	VARCHAR(50)	Sales region name
market	VARCHAR(50)	Market served by the region

sales_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
region_id	INTEGER	Region associated with the transaction
sale_date	DATE	Transaction date
revenue	NUMERIC(12,2)	Transaction revenue
status	VARCHAR(20)	Transaction status

Your question is Running 3-Month Moving 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