Optimizing SQL Windows and Joins | Dataford Interview Questions

How do you optimize complex window functions and joins in high-volume SQL databases? Provide a PostgreSQL query that returns the latest qualifying transaction for each active account, including accounts without a qualifying transaction. Keep filtering as early as practical and make the result deterministic.

Output

One row per active account, with account_id, customer_name, branch_name, latest_credit_date, and latest_credit_amount.
Include accounts with no qualifying transaction, represented by NULL transaction columns.
Return the latest positive CREDIT transaction on or after 2025-01-01; break same-date ties by the greatest txn_id.
Order by account_id ascending.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
customer_name	VARCHAR(100)	Account holder name
branch_id	INT	Referenced branch identifier
status	VARCHAR(20)	Account status

branches

Column	Type	Description
branch_idPK	INT	Unique branch identifier
branch_name	VARCHAR(100)	Branch display name

transactions

Column	Type	Description
txn_idPK	INT	Unique transaction identifier
account_id	INT	Referenced account identifier
txn_date	DATE	Transaction posting date
amount	DECIMAL(12,2)	Transaction amount
txn_type	VARCHAR(20)	Transaction category

Your question is Optimizing SQL Windows and Joins. Start with the requirements and the three 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