Your question is Aggregate Portfolio Account Balances. 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.
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.
2025-01-01 through 2025-03-31, inclusive.| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Unique client identifier |
| client_name | VARCHAR(100) | Client display name |
| risk_tier | VARCHAR(20) | Optional client risk classification |
| 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 |
| 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 |