Top Accounts per Strategy

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Accounts per Strategy. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

Top Accounts per Strategy

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Accounts per Strategy. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results