SQL Window Functions Moving Average | Dataford Interview Questions

AArete's client analytics reporting workflow needs a smoother view of daily revenue from active client accounts. Write a PostgreSQL query that aggregates eligible transactions and calculates a rolling three-day average.

Requirements

Join revenue_transactions to client_accounts and include only accounts with account_status = 'active'.
Calculate total revenue for each transaction date.
Use a window function to calculate the average of the current day's revenue and the two preceding reporting dates. If fewer than three dates exist, average the available rows.
Return results in ascending date order with daily revenue and the moving average rounded to two decimal places.

Schema

client_accounts

Column	Type	Description
account_idPK	INTEGER	Unique client account identifier
account_name	VARCHAR(100)	Client account name
account_status	VARCHAR(20)	Current account status

revenue_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique revenue transaction identifier
account_id	INTEGER	Client account associated with the transaction
revenue_date	DATE	Date on which revenue was recorded
revenue_amount	NUMERIC(12,2)	Revenue amount for the transaction

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