NULLs and Duplicates in Aggregations | Dataford Interview Questions

How do you handle NULL values and duplicates when aggregating financial transaction data?

Write a PostgreSQL query that aggregates posted transactions by account, keeps the most recently loaded record for each non-NULL transaction reference, preserves separate records with NULL references, and treats NULL amounts as zero.

Output

One row per account, including accounts with no qualifying transactions.
Columns: account_id, account_name, posted_transaction_count, and total_amount.
Sort by total_amount descending, then account_id ascending.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique investment account identifier
account_name	VARCHAR(100)	Account display name

financial_transactions

Column	Type	Description
event_idPK	INT	Unique source event identifier
transaction_ref	VARCHAR(40)	Business transaction reference, possibly NULL
account_id	INT	Account associated with the transaction
amount	DECIMAL(12,2)	Transaction amount, possibly NULL
status	VARCHAR(20)	Transaction lifecycle status
loaded_at	TIMESTAMP	Timestamp when the record was loaded

Your question is NULLs and Duplicates in Aggregations. 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