Running Totals by Date | Dataford Interview Questions

ValueMomentum's policy administration reporting feed needs a daily view of posted premium collections from active policy accounts. Write a PostgreSQL query that aggregates qualifying payments by date and calculates the cumulative collection total over time.

Requirements

Join payments to policy accounts and include only accounts whose status is Active.
Include only payments with payment_status = 'Posted'; exclude unmatched accounts and other payment statuses.
Return one row per payment date with daily_collected and a chronological running_total. Dates containing only NULL payment amounts should remain in the result.

Schema

vm_policy_accounts

Column	Type	Description
account_idPK	INTEGER	Unique policy account identifier
account_number	VARCHAR(20)	External policy account number
product_line	VARCHAR(40)	Insurance product line
account_status	VARCHAR(20)	Current account status

vm_premium_payments

Column	Type	Description
payment_idPK	INTEGER	Unique payment identifier
account_id	INTEGER	Policy account receiving the payment
payment_date	DATE	Date the payment was recorded
amount	NUMERIC(12,2)	Payment amount
payment_status	VARCHAR(20)	Payment processing status
payment_method	VARCHAR(20)	Payment method used

Your question is Running Totals by Date. 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