Running Totals With CTEs | Dataford Interview Questions

Alpaca's brokerage ledger needs a chronological cash-balance view for each account. Write a PostgreSQL query that uses a CTE to aggregate settled cash movements by account and date, then uses a correlated subquery and window function to calculate running balances.

Requirements

Include only settled movements from February 2025 and exclude movements for unknown accounts.
Aggregate multiple movements on the same account and date into daily_net_change, treating NULL amounts as zero.
Add each account's starting balance through a correlated subquery, then calculate running_cash_balance in date order for each account.
Return results ordered by account_id and movement_date.

Schema

accounts

Column	Type	Description
account_idPK	VARCHAR(20)	Alpaca brokerage account identifier
initial_cash_balance	NUMERIC(12,2)	Cash balance before the reporting period

cash_movements

Column	Type	Description
movement_idPK	INT	Unique cash movement identifier
account_id	VARCHAR(20)	Account associated with the movement
movement_date	DATE	Settlement date of the movement
amount	NUMERIC(12,2)	Signed cash movement amount
status	VARCHAR(20)	Processing status of the movement

Your question is Running Totals With CTEs. 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