Window Functions for Rolling Stats | Dataford Interview Questions

PwC's Halo analytics platform needs a client-level view of transaction activity for the reporting period from January 1 through February 15, 2025. Write a PostgreSQL query that aggregates daily transaction volumes, calculates a cumulative total, and computes a trailing 30-day moving average for each active client.

Requirements

Join clients to client_transactions and include only active clients.
Aggregate transaction volume by client and transaction date. Treat a NULL transaction volume as zero.
Calculate a chronological running total for each client.
Calculate the average of recorded daily volumes from the current date and preceding 29 calendar days. Dates without transactions do not contribute rows to the average.
Return results ordered by client ID and transaction date.

Schema

clients

Column	Type	Description
client_idPK	INTEGER	Unique client identifier
client_name	VARCHAR(100)	Client display name
segment	VARCHAR(50)	Client market segment
is_active	BOOLEAN	Whether the client is currently active

client_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
client_id	INTEGER	Client associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
transaction_volume	NUMERIC(14,2)	Transaction volume

Your question is Window Functions for Rolling Stats. 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