Aggregate Daily Returns Query | Dataford Interview Questions

Aqr's portfolio tracking system stores portfolio metadata separately from daily return observations. Write a PostgreSQL query that summarizes active portfolios between 2025-01-01 and 2025-01-03.

Requirements

Join portfolio metadata to return observations and exclude inactive portfolios.
Return one row per portfolio and return date, with the summed daily return percentage.
Include the number of non-null return observations and the number of positive observations.
Sort results by return date ascending, then portfolio name ascending. Treat a day containing only null return values as a zero aggregate return.

Schema

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
portfolio_name	VARCHAR(100)	Aqr portfolio name
portfolio_type	VARCHAR(50)	Portfolio strategy classification
is_active	BOOLEAN	Whether the portfolio is currently active

portfolio_return_observations

Column	Type	Description
observation_idPK	INT	Unique return observation identifier
portfolio_id	INT	Portfolio associated with the observation
return_date	DATE	Date of the return observation
return_pct	DECIMAL(10,6)	Return percentage for the observation

Your question is Aggregate Daily Returns Query. 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