Aggregate Portfolio Account Balances

MediumSQL · PostgreSQL

Problem

Envestnet portfolio reporting needs a view of client assets consolidated across portfolios. Write a PostgreSQL query that aggregates recorded ending balances by client and asset class for a specified date range, then identifies each client's two highest-balance asset classes.

Requirements

Include only holdings from 2025-01-01 through 2025-03-31, inclusive.
Aggregate balances across all matching portfolios for each client and asset class, treating NULL balances as zero.
Use a window function to rank asset classes within each client by aggregated balance, descending.
Return only the top two asset classes per client, including the number of contributing portfolios, and sort the result by client name, rank, and asset class.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(100)	Client display name
risk_tier	VARCHAR(20)	Optional client risk classification

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
client_id	INT	Owning client identifier
portfolio_name	VARCHAR(100)	Portfolio display name
account_type	VARCHAR(30)	Portfolio account type

holdings

Column	Type	Description
holding_idPK	INT	Unique holding snapshot identifier
portfolio_id	INT	Related portfolio identifier
as_of_date	DATE	Date of the balance snapshot
asset_class	VARCHAR(50)	Asset class assigned to the holding
ending_balance	NUMERIC(14,2)	Ending market value for the snapshot

Tablesclientsportfoliosholdings