Top Assets Per Portfolio Query | Dataford Interview Questions

Your question is Top Assets Per Portfolio Query. 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.

Problem

Capital Group's investment analytics team needs a quarterly view of the strongest assets held in each portfolio. For this exercise, treat the last quarter as Q2 2025, from April 1 through June 30, 2025.

Write a PostgreSQL query that identifies the top three performing assets in every portfolio.

Requirements

Join portfolios, assets, and asset performance records.
Include only performance observations in Q2 2025, using an inclusive start date and exclusive end date.
Aggregate each asset's return_pct across the quarter.
Rank assets independently within each portfolio and return at most three per portfolio, ordered by performance descending. Use asset_id as a deterministic tie-breaker.

Schema

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
portfolio_name	VARCHAR(100)	Portfolio display name

assets

Column	Type	Description
asset_idPK	INT	Unique asset identifier
portfolio_id	INT	Portfolio holding the asset
asset_name	VARCHAR(150)	Asset name
ticker	VARCHAR(20)	Trading symbol
asset_class	VARCHAR(50)	Asset classification

asset_performance

Column	Type	Description
performance_idPK	INT	Unique performance observation identifier
asset_id	INT	Asset associated with the observation
observation_date	DATE	Observation date
return_pct	NUMERIC(8,2)	Return percentage for the observation