Rolling Three-Month Average Query | Dataford Interview Questions

Teradata Vantage reporting needs a monthly sales trend for active sales regions. Write a PostgreSQL-compatible SQL query that calculates monthly sales and a rolling three-month average for each active region from January through April 2025.

Requirements

Aggregate transaction amounts by region and calendar month.
Include months with no transactions as zero sales.
Calculate the average for the current month and up to the two preceding calendar months within each region.
Return results ordered by region name and month.

Output details

Return columns in this order: region_name, month_start, monthly_sales, and rolling_3_month_avg.
Order rows by region_name ascending, then month_start ascending.
Round rolling_3_month_avg to two decimal places.
Treat missing monthly sales as numeric 0.00 when calculating the rolling average.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique identifier for a sales region
region_name	VARCHAR(100)	Display name of the sales region
is_active	BOOLEAN	Whether the region is included in reporting

sales_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
region_id	INT	Region associated with the transaction
sale_date	DATE	Date on which the sale occurred
amount	DECIMAL(12,2)	Sales amount, which may be NULL

Your question is Rolling Three-Month Average Query. 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