Running Transaction Total with SQL | Dataford Interview Questions

Ameriprise needs transaction-level activity metrics for clients using its financial data platforms. Write a PostgreSQL query that calculates each client's rolling transaction total across the thirty-day period ending on every transaction date.

Requirements

Join transactions to clients and display the client name, using Unknown when a transaction has no matching client.
Calculate a rolling sum of amount for each client, partitioned by client_id and ordered by transaction_date.
Include transactions with negative amounts, zero amounts, and NULL amounts. NULL amounts should not contribute to the sum.
Return results ordered by client, transaction date, and transaction ID. The thirty-day window is inclusive of the transaction date and the preceding 30 calendar days.

Schema

clients

Column	Type	Description
client_idPK	INTEGER	Unique Ameriprise client identifier
client_name	VARCHAR(100)	Client display name

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
client_id	INTEGER	Client associated with the transaction
transaction_date	DATE	Transaction posting date
amount	NUMERIC(12,2)	Transaction amount, including reversals

Your question is Running Transaction Total with SQL. 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