Optimizing Window Functions at Scale | Dataford Interview Questions

How do you optimize a query that utilizes a window function on a massive, distributed dataset at Plaid?

Use the provided PostgreSQL tables and write the query for completed January transactions. The solution should reduce the data processed by the window calculation while producing deterministic results.

Output

One row per account and transaction date with completed transactions
Columns: account_label, transaction_date, daily_net_amount, running_balance, and prior_running_balance
Order by account_label, then transaction_date

Schema

plaid_accounts

Column	Type	Description
account_idPK	INT	Unique Plaid account identifier
account_label	VARCHAR(100)	Display label for the account
region	VARCHAR(30)	Account region

plaid_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Referenced Plaid account
transaction_date	DATE	Posting date of the transaction
amount	NUMERIC(12,2)	Signed transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is Optimizing Window Functions at Scale. 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