Joins vs Window Functions | Dataford Interview Questions

How do you decide between optimizing joins and using window functions for performance?

Using the provided accounts and transactions tables, write a PostgreSQL query that produces the requested account totals and rankings, then explain the performance reasoning behind the approach.

Output

One row per active account with posted, non-null transactions included for January and February 2025.
Columns: account_id, institution_name, total_amount, and institution_rank.
Include active accounts with no qualifying transactions as zero totals. Rank within each institution by descending total, allowing ties, and sort by institution, rank, then account ID.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
institution_name	VARCHAR(100)	Institution associated with the account
is_active	BOOLEAN	Whether the account is active

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
transaction_date	DATE	Date the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
transaction_status	VARCHAR(20)	Transaction processing status

Your question is Joins vs Window Functions. 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