Describe the SQL queries you would use to fetch, filter, and aggregate data from your designed database in an iCapital-like scenario.
Use the provided portfolio, advisor, and transaction tables. Return active portfolios belonging to active advisors, including portfolios with no qualifying transactions. Include only settled transactions from Q1 2025.
Output
- One row per qualifying portfolio with
portfolio_id, advisor_name, portfolio_name, settled_transaction_count, net_cash_flow, and last_transaction_date.
- Treat contributions as positive, withdrawals and fees as negative, and missing amounts as zero.
- Order by
net_cash_flow descending, then portfolio_id ascending.
