Top Accounts per Strategy

MediumSQL · PostgreSQL

Problem

AllianceBernstein needs a daily trading-performance view for accounts managed through its portfolio platforms. Write a PostgreSQL query that aggregates eligible trades by account and trade date, then identifies the highest-performing accounts within each strategy.

Requirements

Join trades to accounts and strategies.
Include only active accounts and trades from January 1 through January 3, 2025.
Aggregate daily trade count, notional volume, and realized P&L per account.
Calculate each account's total P&L across the date range.
Use a window function to rank accounts within each strategy by total P&L, retaining the top two ranks. Preserve ties.
Return daily rows for qualifying accounts, ordered by strategy, performance rank, date, and account.

Schema

strategies

Column	Type	Description
strategy_idPK	INT	Unique strategy identifier
strategy_name	VARCHAR(100)	Display name of the investment strategy
asset_class	VARCHAR(50)	Primary asset class for the strategy

accounts

Column	Type	Description
account_idPK	INT	Unique portfolio account identifier
strategy_id	INT	Assigned investment strategy
account_name	VARCHAR(100)	Portfolio account name
status	VARCHAR(20)	Account lifecycle status

portfolio_trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
account_id	INT	Account executing the trade
trade_date	DATE	Trade execution date
trade_type	VARCHAR(10)	Buy or sell indicator
quantity	INT	Number of units traded
notional_value	NUMERIC(14,2)	Trade notional value
realized_pnl	NUMERIC(14,2)	Realized profit or loss from the trade

Tablesstrategiesaccountsportfolio_trades