{"queries":[{"id":"equity_portfolio_metrics_with_deltas","compiledQueryString":"-- Equity Portfolio Metrics with Deltas\n-- Aggregates fund-level performance metrics across all equity funds\n-- Includes quarter-over-quarter deltas for key metrics\n-- Filters to fund_type='EQUITY' to show only PE fund metrics\n\nwith latest_period as (\n select max(period_end_date) as max_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n),\n\nprevious_period as (\n select max(period_end_date) as prev_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date < (select max_date from latest_period)\n),\n\ncurrent_metrics as (\n select\n sum(fund_nav) as total_nav,\n sum(total_commitments) as total_commitments,\n sum(total_called_capital) as total_called_capital,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(total_distributions) as total_distributions,\n -- Portfolio-level PE metrics\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as tvpi_portfolio,\n -- Portfolio statistics\n count(distinct fund_id) as number_of_equity_funds,\n sum(number_of_portfolio_companies) as total_portfolio_companies,\n sum(number_of_positions) as total_positions\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date = (select max_date from latest_period)\n),\n\nprior_metrics as (\n select\n sum(fund_nav) as prev_total_nav,\n sum(unfunded_commitment) as prev_unfunded_commitment,\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as prev_dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as prev_rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as prev_tvpi_portfolio\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date = (select prev_date from previous_period)\n)\n\nselect\n -- Current values\n c.total_nav,\n c.total_commitments,\n c.total_called_capital,\n c.unfunded_commitment,\n c.total_distributions,\n c.dpi_portfolio,\n c.rvpi_portfolio,\n c.tvpi_portfolio,\n c.number_of_equity_funds,\n c.total_portfolio_companies,\n c.total_positions,\n \n -- Previous period values\n p.prev_total_nav,\n p.prev_unfunded_commitment,\n p.prev_dpi_portfolio,\n p.prev_rvpi_portfolio,\n p.prev_tvpi_portfolio,\n \n -- Delta calculations\n -- NAV: percent delta\n case \n when p.prev_total_nav is not null and p.prev_total_nav != 0\n then (c.total_nav - p.prev_total_nav) / p.prev_total_nav\n else null\n end as nav_delta_pct,\n \n -- TVPI: absolute delta\n case \n when p.prev_tvpi_portfolio is not null\n then c.tvpi_portfolio - p.prev_tvpi_portfolio\n else null\n end as tvpi_delta,\n \n -- DPI: absolute delta\n case \n when p.prev_dpi_portfolio is not null\n then c.dpi_portfolio - p.prev_dpi_portfolio\n else null\n end as dpi_delta,\n \n -- RVPI: absolute delta\n case \n when p.prev_rvpi_portfolio is not null\n then c.rvpi_portfolio - p.prev_rvpi_portfolio\n else null\n end as rvpi_delta,\n \n -- Unfunded Commitment: absolute delta\n case \n when p.prev_unfunded_commitment is not null\n then c.unfunded_commitment - p.prev_unfunded_commitment\n else null\n end as unfunded_delta,\n \n -- Validation checks (private markets realism)\n -- TVPI should always be >= DPI\n case \n when c.tvpi_portfolio < c.dpi_portfolio \n then 'WARNING: TVPI < DPI (impossible)'\n else 'OK'\n end as tvpi_dpi_check,\n \n -- RVPI should equal TVPI - DPI (within rounding)\n case \n when abs(c.rvpi_portfolio - (c.tvpi_portfolio - c.dpi_portfolio)) > 0.01\n then 'WARNING: RVPI != TVPI - DPI'\n else 'OK'\n end as rvpi_consistency_check\n\nfrom current_metrics c\ncross join prior_metrics p","inputQueryString":"-- Equity Portfolio Metrics with Deltas\n-- Aggregates fund-level performance metrics across all equity funds\n-- Includes quarter-over-quarter deltas for key metrics\n-- Filters to fund_type='EQUITY' to show only PE fund metrics\n\nwith latest_period as (\n select max(period_end_date) as max_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n),\n\nprevious_period as (\n select max(period_end_date) as prev_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date < (select max_date from latest_period)\n),\n\ncurrent_metrics as (\n select\n sum(fund_nav) as total_nav,\n sum(total_commitments) as total_commitments,\n sum(total_called_capital) as total_called_capital,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(total_distributions) as total_distributions,\n -- Portfolio-level PE metrics\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as tvpi_portfolio,\n -- Portfolio statistics\n count(distinct fund_id) as number_of_equity_funds,\n sum(number_of_portfolio_companies) as total_portfolio_companies,\n sum(number_of_positions) as total_positions\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date = (select max_date from latest_period)\n),\n\nprior_metrics as (\n select\n sum(fund_nav) as prev_total_nav,\n sum(unfunded_commitment) as prev_unfunded_commitment,\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as prev_dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as prev_rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as prev_tvpi_portfolio\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n and period_end_date = (select prev_date from previous_period)\n)\n\nselect\n -- Current values\n c.total_nav,\n c.total_commitments,\n c.total_called_capital,\n c.unfunded_commitment,\n c.total_distributions,\n c.dpi_portfolio,\n c.rvpi_portfolio,\n c.tvpi_portfolio,\n c.number_of_equity_funds,\n c.total_portfolio_companies,\n c.total_positions,\n \n -- Previous period values\n p.prev_total_nav,\n p.prev_unfunded_commitment,\n p.prev_dpi_portfolio,\n p.prev_rvpi_portfolio,\n p.prev_tvpi_portfolio,\n \n -- Delta calculations\n -- NAV: percent delta\n case \n when p.prev_total_nav is not null and p.prev_total_nav != 0\n then (c.total_nav - p.prev_total_nav) / p.prev_total_nav\n else null\n end as nav_delta_pct,\n \n -- TVPI: absolute delta\n case \n when p.prev_tvpi_portfolio is not null\n then c.tvpi_portfolio - p.prev_tvpi_portfolio\n else null\n end as tvpi_delta,\n \n -- DPI: absolute delta\n case \n when p.prev_dpi_portfolio is not null\n then c.dpi_portfolio - p.prev_dpi_portfolio\n else null\n end as dpi_delta,\n \n -- RVPI: absolute delta\n case \n when p.prev_rvpi_portfolio is not null\n then c.rvpi_portfolio - p.prev_rvpi_portfolio\n else null\n end as rvpi_delta,\n \n -- Unfunded Commitment: absolute delta\n case \n when p.prev_unfunded_commitment is not null\n then c.unfunded_commitment - p.prev_unfunded_commitment\n else null\n end as unfunded_delta,\n \n -- Validation checks (private markets realism)\n -- TVPI should always be >= DPI\n case \n when c.tvpi_portfolio < c.dpi_portfolio \n then 'WARNING: TVPI < DPI (impossible)'\n else 'OK'\n end as tvpi_dpi_check,\n \n -- RVPI should equal TVPI - DPI (within rounding)\n case \n when abs(c.rvpi_portfolio - (c.tvpi_portfolio - c.dpi_portfolio)) > 0.01\n then 'WARNING: RVPI != TVPI - DPI'\n else 'OK'\n end as rvpi_consistency_check\n\nfrom current_metrics c\ncross join prior_metrics p","compiled":false,"inline":false},{"id":"equity_portfolio_timeseries","compiledQueryString":"-- Equity Portfolio Time Series\n-- Shows NAV and other metrics over time for equity funds only\n-- Filters to fund_type='EQUITY' to show only PE fund trends\n\nselect\n period_end_date,\n sum(fund_nav) as total_nav,\n sum(total_called_capital) as total_called_capital,\n sum(total_distributions) as total_distributions,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(period_net_flows) as net_cash_contributions_period,\n -- Portfolio-level PE metrics over time\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as tvpi_portfolio\nfrom metrics_fund_performance\nwhere fund_type = 'EQUITY'\ngroup by period_end_date\norder by period_end_date","inputQueryString":"-- Equity Portfolio Time Series\n-- Shows NAV and other metrics over time for equity funds only\n-- Filters to fund_type='EQUITY' to show only PE fund trends\n\nselect\n period_end_date,\n sum(fund_nav) as total_nav,\n sum(total_called_capital) as total_called_capital,\n sum(total_distributions) as total_distributions,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(period_net_flows) as net_cash_contributions_period,\n -- Portfolio-level PE metrics over time\n sum(total_distributions) / nullif(sum(total_called_capital), 0) as dpi_portfolio,\n sum(fund_nav) / nullif(sum(total_called_capital), 0) as rvpi_portfolio,\n (sum(fund_nav) + sum(total_distributions)) / nullif(sum(total_called_capital), 0) as tvpi_portfolio\nfrom metrics_fund_performance\nwhere fund_type = 'EQUITY'\ngroup by period_end_date\norder by period_end_date","compiled":false,"inline":false},{"id":"equity_capital_activity","compiledQueryString":"-- Capital Activity by Period for Equity Funds\n-- Shows contributions and distributions separately to visualize J-curve\n\nwith instruments as (\n select\n instrument_id,\n fund_id,\n instrument_type\n from dim_instruments\n where instrument_type = 'EQUITY'\n),\n\nperiod_cashflows as (\n select\n (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date as period_end_date,\n -- Contributions (negative cashflows from LP perspective)\n sum(\n case \n when cf.cashflow_type in ('INVESTMENT', 'CONTRIBUTION', 'PURCHASE') \n then cf.cashflow_amount\n else 0\n end\n ) as period_contributions,\n -- Distributions (positive cashflows to LP)\n sum(\n case \n when cf.cashflow_type in ('EXIT_PROCEEDS', 'DISTRIBUTION', 'DIVIDEND', 'SALE')\n then cf.cashflow_amount\n else 0\n end\n ) as period_distributions\n from metrics_returns_cashflows cf\n inner join instruments i on cf.instrument_id = i.instrument_id\n group by (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date\n)\n\nselect\n period_end_date,\n sum(period_contributions) as contributions,\n sum(period_distributions) as distributions,\n sum(period_distributions) - sum(period_contributions) as net_cashflow\nfrom period_cashflows\ngroup by period_end_date\norder by period_end_date","inputQueryString":"-- Capital Activity by Period for Equity Funds\n-- Shows contributions and distributions separately to visualize J-curve\n\nwith instruments as (\n select\n instrument_id,\n fund_id,\n instrument_type\n from dim_instruments\n where instrument_type = 'EQUITY'\n),\n\nperiod_cashflows as (\n select\n (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date as period_end_date,\n -- Contributions (negative cashflows from LP perspective)\n sum(\n case \n when cf.cashflow_type in ('INVESTMENT', 'CONTRIBUTION', 'PURCHASE') \n then cf.cashflow_amount\n else 0\n end\n ) as period_contributions,\n -- Distributions (positive cashflows to LP)\n sum(\n case \n when cf.cashflow_type in ('EXIT_PROCEEDS', 'DISTRIBUTION', 'DIVIDEND', 'SALE')\n then cf.cashflow_amount\n else 0\n end\n ) as period_distributions\n from metrics_returns_cashflows cf\n inner join instruments i on cf.instrument_id = i.instrument_id\n group by (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date\n)\n\nselect\n period_end_date,\n sum(period_contributions) as contributions,\n sum(period_distributions) as distributions,\n sum(period_distributions) - sum(period_contributions) as net_cashflow\nfrom period_cashflows\ngroup by period_end_date\norder by period_end_date","compiled":false,"inline":false},{"id":"credit_portfolio_metrics","compiledQueryString":"-- Credit Portfolio Metrics\n-- Aggregates fund-level performance metrics across all credit funds\n-- Filters to fund_type='CREDIT' to show only credit fund metrics\n-- Uses the latest period that has credit-specific data populated\n\nwith latest_period_with_data as (\n select max(period_end_date) as max_date\n from metrics_fund_performance\n where fund_type = 'CREDIT'\n and principal_outstanding is not null\n)\n\nselect\n -- Capital deployment metrics\n sum(total_commitments) as lp_commitments,\n sum(total_called_capital) as called_capital,\n coalesce(sum(total_exposure), 0) as facility_commitments,\n coalesce(sum(principal_outstanding), 0) as principal_outstanding,\n coalesce(sum(undrawn_commitment), 0) as undrawn_facilities,\n \n -- Calculated deployment metrics\n sum(total_called_capital) - coalesce(sum(total_exposure), 0) as cash_and_reserves,\n sum(unfunded_commitment) as unfunded_lp_commitment,\n \n -- Deployment rates\n case \n when sum(total_commitments) > 0 \n then coalesce(sum(total_exposure), 0) / sum(total_commitments) \n else 0 \n end as facility_deployment_rate,\n case \n when coalesce(sum(total_exposure), 0) > 0 \n then coalesce(sum(principal_outstanding), 0) / coalesce(sum(total_exposure), 0)\n else 0 \n end as facility_utilization_rate,\n \n -- Legacy fields (for backward compatibility)\n sum(total_commitments) as total_commitments,\n coalesce(sum(total_exposure), 0) as total_exposure,\n coalesce(sum(principal_outstanding), 0) as total_principal_outstanding,\n coalesce(sum(undrawn_commitment), 0) as total_undrawn_commitment,\n \n -- Other metrics\n sum(total_distributions) as total_distributions,\n coalesce(sum(interest_income), 0) as total_interest_income,\n \n -- Portfolio statistics\n count(distinct fund_id) as number_of_credit_funds,\n sum(number_of_portfolio_companies) as total_portfolio_companies,\n sum(number_of_positions) as total_positions\nfrom metrics_fund_performance\nwhere fund_type = 'CREDIT'\n and period_end_date = (select max_date from latest_period_with_data)","inputQueryString":"-- Credit Portfolio Metrics\n-- Aggregates fund-level performance metrics across all credit funds\n-- Filters to fund_type='CREDIT' to show only credit fund metrics\n-- Uses the latest period that has credit-specific data populated\n\nwith latest_period_with_data as (\n select max(period_end_date) as max_date\n from metrics_fund_performance\n where fund_type = 'CREDIT'\n and principal_outstanding is not null\n)\n\nselect\n -- Capital deployment metrics\n sum(total_commitments) as lp_commitments,\n sum(total_called_capital) as called_capital,\n coalesce(sum(total_exposure), 0) as facility_commitments,\n coalesce(sum(principal_outstanding), 0) as principal_outstanding,\n coalesce(sum(undrawn_commitment), 0) as undrawn_facilities,\n \n -- Calculated deployment metrics\n sum(total_called_capital) - coalesce(sum(total_exposure), 0) as cash_and_reserves,\n sum(unfunded_commitment) as unfunded_lp_commitment,\n \n -- Deployment rates\n case \n when sum(total_commitments) > 0 \n then coalesce(sum(total_exposure), 0) / sum(total_commitments) \n else 0 \n end as facility_deployment_rate,\n case \n when coalesce(sum(total_exposure), 0) > 0 \n then coalesce(sum(principal_outstanding), 0) / coalesce(sum(total_exposure), 0)\n else 0 \n end as facility_utilization_rate,\n \n -- Legacy fields (for backward compatibility)\n sum(total_commitments) as total_commitments,\n coalesce(sum(total_exposure), 0) as total_exposure,\n coalesce(sum(principal_outstanding), 0) as total_principal_outstanding,\n coalesce(sum(undrawn_commitment), 0) as total_undrawn_commitment,\n \n -- Other metrics\n sum(total_distributions) as total_distributions,\n coalesce(sum(interest_income), 0) as total_interest_income,\n \n -- Portfolio statistics\n count(distinct fund_id) as number_of_credit_funds,\n sum(number_of_portfolio_companies) as total_portfolio_companies,\n sum(number_of_positions) as total_positions\nfrom metrics_fund_performance\nwhere fund_type = 'CREDIT'\n and period_end_date = (select max_date from latest_period_with_data)","compiled":false,"inline":false},{"id":"credit_portfolio_timeseries","compiledQueryString":"-- Credit Portfolio Time Series\n-- Shows principal outstanding, total exposure, and cashflows over time for credit funds\n-- Filters to fund_type='CREDIT' to show only credit fund trends\n\nselect\n period_end_date,\n sum(principal_outstanding) as total_principal_outstanding,\n sum(total_exposure) as total_exposure,\n sum(undrawn_commitment) as total_undrawn_commitment,\n sum(interest_income) as total_interest_income,\n sum(period_net_flows) as net_cash_contributions_period,\n sum(total_commitments) as total_commitments,\n sum(total_called_capital) as total_called_capital\nfrom metrics_fund_performance\nwhere fund_type = 'CREDIT'\ngroup by period_end_date\norder by period_end_date","inputQueryString":"-- Credit Portfolio Time Series\n-- Shows principal outstanding, total exposure, and cashflows over time for credit funds\n-- Filters to fund_type='CREDIT' to show only credit fund trends\n\nselect\n period_end_date,\n sum(principal_outstanding) as total_principal_outstanding,\n sum(total_exposure) as total_exposure,\n sum(undrawn_commitment) as total_undrawn_commitment,\n sum(interest_income) as total_interest_income,\n sum(period_net_flows) as net_cash_contributions_period,\n sum(total_commitments) as total_commitments,\n sum(total_called_capital) as total_called_capital\nfrom metrics_fund_performance\nwhere fund_type = 'CREDIT'\ngroup by period_end_date\norder by period_end_date","compiled":false,"inline":false},{"id":"credit_capital_activity","compiledQueryString":"-- Capital Activity by Period for Credit Funds\n-- Shows draws and repayments separately\n\nwith instruments as (\n select\n instrument_id,\n fund_id,\n instrument_type\n from dim_instruments\n where instrument_type = 'CREDIT'\n),\n\nperiod_cashflows as (\n select\n (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date as period_end_date,\n -- Draws (negative cashflows from LP perspective)\n sum(\n case \n when cf.cashflow_type in ('DRAW', 'CONTRIBUTION') \n then cf.cashflow_amount\n else 0\n end\n ) as period_contributions,\n -- Repayments (positive cashflows to LP)\n sum(\n case \n when cf.cashflow_type in ('PRINCIPAL', 'PREPAYMENT', 'INTEREST')\n then cf.cashflow_amount\n else 0\n end\n ) as period_distributions\n from metrics_returns_cashflows cf\n inner join instruments i on cf.instrument_id = i.instrument_id\n group by (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date\n)\n\nselect\n period_end_date,\n sum(period_contributions) as contributions,\n sum(period_distributions) as distributions,\n sum(period_distributions) - sum(period_contributions) as net_cashflow\nfrom period_cashflows\ngroup by period_end_date\norder by period_end_date","inputQueryString":"-- Capital Activity by Period for Credit Funds\n-- Shows draws and repayments separately\n\nwith instruments as (\n select\n instrument_id,\n fund_id,\n instrument_type\n from dim_instruments\n where instrument_type = 'CREDIT'\n),\n\nperiod_cashflows as (\n select\n (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date as period_end_date,\n -- Draws (negative cashflows from LP perspective)\n sum(\n case \n when cf.cashflow_type in ('DRAW', 'CONTRIBUTION') \n then cf.cashflow_amount\n else 0\n end\n ) as period_contributions,\n -- Repayments (positive cashflows to LP)\n sum(\n case \n when cf.cashflow_type in ('PRINCIPAL', 'PREPAYMENT', 'INTEREST')\n then cf.cashflow_amount\n else 0\n end\n ) as period_distributions\n from metrics_returns_cashflows cf\n inner join instruments i on cf.instrument_id = i.instrument_id\n group by (date_trunc('quarter', cf.cashflow_date) + interval '3 months' - interval '1 day')::date\n)\n\nselect\n period_end_date,\n sum(period_contributions) as contributions,\n sum(period_distributions) as distributions,\n sum(period_distributions) - sum(period_contributions) as net_cashflow\nfrom period_cashflows\ngroup by period_end_date\norder by period_end_date","compiled":false,"inline":false},{"id":"sector_exposure","compiledQueryString":"select \n industry_name,\n instrument_type,\n sum(deployed_capital_usd) as exposure_usd\nfrom metrics_exposure_timeseries\nwhere period_type = 'Current'\ngroup by industry_name, instrument_type\norder by exposure_usd desc","inputQueryString":"select \n industry_name,\n instrument_type,\n sum(deployed_capital_usd) as exposure_usd\nfrom metrics_exposure_timeseries\nwhere period_type = 'Current'\ngroup by industry_name, instrument_type\norder by exposure_usd desc","compiled":false,"inline":false},{"id":"country_exposure","compiledQueryString":"select \n country_code,\n country_name,\n instrument_type,\n sum(deployed_capital_usd) as exposure\nfrom metrics_exposure_timeseries\nwhere period_type = 'Current'\ngroup by country_code, country_name, instrument_type\norder by exposure desc","inputQueryString":"select \n country_code,\n country_name,\n instrument_type,\n sum(deployed_capital_usd) as exposure\nfrom metrics_exposure_timeseries\nwhere period_type = 'Current'\ngroup by country_code, country_name, instrument_type\norder by exposure desc","compiled":false,"inline":false},{"id":"top_contributors","compiledQueryString":"with latest as (\n select max(period_end_date) as max_date \n from metrics_position_performance\n)\nselect \n mp.company_id,\n '/companies/' || mp.company_id as company_link,\n mp.company_name,\n mp.fund_name,\n mp.instrument_name,\n mp.instrument_type,\n mp.fair_value as current_fair_value,\n mp.cost_basis,\n (mp.fair_value + coalesce(mp.realized_proceeds, 0) - mp.cost_basis) as total_return,\n mp.moic,\n mp.equity_irr_approx as irr_approx\nfrom metrics_position_performance mp\njoin latest on mp.period_end_date = latest.max_date\nwhere mp.instrument_type = 'EQUITY' and mp.cost_basis > 0\norder by total_return desc nulls last\nlimit 20","inputQueryString":"with latest as (\n select max(period_end_date) as max_date \n from metrics_position_performance\n)\nselect \n mp.company_id,\n '/companies/' || mp.company_id as company_link,\n mp.company_name,\n mp.fund_name,\n mp.instrument_name,\n mp.instrument_type,\n mp.fair_value as current_fair_value,\n mp.cost_basis,\n (mp.fair_value + coalesce(mp.realized_proceeds, 0) - mp.cost_basis) as total_return,\n mp.moic,\n mp.equity_irr_approx as irr_approx\nfrom metrics_position_performance mp\njoin latest on mp.period_end_date = latest.max_date\nwhere mp.instrument_type = 'EQUITY' and mp.cost_basis > 0\norder by total_return desc nulls last\nlimit 20","compiled":false,"inline":false},{"id":"top_detractors","compiledQueryString":"with latest as (\n select max(period_end_date) as max_date \n from metrics_position_performance\n)\nselect \n mp.company_id,\n '/companies/' || mp.company_id as company_link,\n mp.company_name,\n mp.fund_name,\n mp.instrument_name,\n mp.instrument_type,\n mp.fair_value as current_fair_value,\n mp.cost_basis,\n (mp.fair_value + coalesce(mp.realized_proceeds, 0) - mp.cost_basis) as total_return,\n mp.moic,\n mp.equity_irr_approx as irr_approx\nfrom metrics_position_performance mp\njoin latest on mp.period_end_date = latest.max_date\nwhere mp.instrument_type = 'EQUITY' and mp.cost_basis > 0\norder by total_return asc nulls last\nlimit 20","inputQueryString":"with latest as (\n select max(period_end_date) as max_date \n from metrics_position_performance\n)\nselect \n mp.company_id,\n '/companies/' || mp.company_id as company_link,\n mp.company_name,\n mp.fund_name,\n mp.instrument_name,\n mp.instrument_type,\n mp.fair_value as current_fair_value,\n mp.cost_basis,\n (mp.fair_value + coalesce(mp.realized_proceeds, 0) - mp.cost_basis) as total_return,\n mp.moic,\n mp.equity_irr_approx as irr_approx\nfrom metrics_position_performance mp\njoin latest on mp.period_end_date = latest.max_date\nwhere mp.instrument_type = 'EQUITY' and mp.cost_basis > 0\norder by total_return asc nulls last\nlimit 20","compiled":false,"inline":false},{"id":"portfolio_positions","compiledQueryString":"-- All portfolio positions for scatter plot analysis\nwith latest_instrument_snapshots as (\n select\n instrument_id,\n max(period_end_date) as latest_period_end_date\n from metrics_position_performance\n group by instrument_id\n)\n\nselect \n mpp.instrument_id,\n mpp.instrument_name,\n mpp.company_name,\n mpp.fund_name,\n mpp.instrument_type,\n mpp.cost_basis,\n mpp.fair_value as current_fair_value,\n (mpp.fair_value + coalesce(mpp.realized_proceeds, 0)) as total_value,\n mpp.moic as gross_moic,\n mpp.equity_irr_approx as gross_irr_approx,\n mpp.holding_period_years\nfrom metrics_position_performance mpp\ninner join latest_instrument_snapshots lis\n on mpp.instrument_id = lis.instrument_id\n and mpp.period_end_date = lis.latest_period_end_date\nwhere mpp.instrument_type = 'EQUITY' and mpp.cost_basis > 0\norder by mpp.moic desc","inputQueryString":"-- All portfolio positions for scatter plot analysis\nwith latest_instrument_snapshots as (\n select\n instrument_id,\n max(period_end_date) as latest_period_end_date\n from metrics_position_performance\n group by instrument_id\n)\n\nselect \n mpp.instrument_id,\n mpp.instrument_name,\n mpp.company_name,\n mpp.fund_name,\n mpp.instrument_type,\n mpp.cost_basis,\n mpp.fair_value as current_fair_value,\n (mpp.fair_value + coalesce(mpp.realized_proceeds, 0)) as total_value,\n mpp.moic as gross_moic,\n mpp.equity_irr_approx as gross_irr_approx,\n mpp.holding_period_years\nfrom metrics_position_performance mpp\ninner join latest_instrument_snapshots lis\n on mpp.instrument_id = lis.instrument_id\n and mpp.period_end_date = lis.latest_period_end_date\nwhere mpp.instrument_type = 'EQUITY' and mpp.cost_basis > 0\norder by mpp.moic desc","compiled":false,"inline":false},{"id":"credit_maturity_ladder","compiledQueryString":"-- Credit Maturity Ladder\n-- Shows principal outstanding by maturity year for credit instruments\n-- Provides visibility into loan maturity concentration and refinancing risk\n\nselect\n maturity_year,\n sum(principal_outstanding) as principal_maturing,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and maturity_year is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by maturity_year\norder by maturity_year","inputQueryString":"-- Credit Maturity Ladder\n-- Shows principal outstanding by maturity year for credit instruments\n-- Provides visibility into loan maturity concentration and refinancing risk\n\nselect\n maturity_year,\n sum(principal_outstanding) as principal_maturing,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and maturity_year is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by maturity_year\norder by maturity_year","compiled":false,"inline":false},{"id":"credit_yield_distribution","compiledQueryString":"-- Credit Yield Distribution\n-- Shows yield distribution across credit instruments for portfolio analysis\n-- Provides visibility into yield characteristics by security rank and interest index\n\nselect\n security_rank,\n interest_index,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies,\n sum(principal_outstanding) as total_principal_outstanding,\n -- Yield statistics\n avg(all_in_yield) as avg_yield,\n min(all_in_yield) as min_yield,\n max(all_in_yield) as max_yield,\n avg(spread_bps) as avg_spread_bps,\n min(spread_bps) as min_spread_bps,\n max(spread_bps) as max_spread_bps,\n -- Yield buckets for distribution analysis\n case\n when all_in_yield < 0.05 then '0-5%'\n when all_in_yield >= 0.05 and all_in_yield < 0.08 then '5-8%'\n when all_in_yield >= 0.08 and all_in_yield < 0.10 then '8-10%'\n when all_in_yield >= 0.10 and all_in_yield < 0.12 then '10-12%'\n when all_in_yield >= 0.12 then '12%+'\n else 'Unknown'\n end as yield_bucket\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and all_in_yield is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by security_rank, interest_index, yield_bucket\norder by security_rank, avg_yield desc","inputQueryString":"-- Credit Yield Distribution\n-- Shows yield distribution across credit instruments for portfolio analysis\n-- Provides visibility into yield characteristics by security rank and interest index\n\nselect\n security_rank,\n interest_index,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies,\n sum(principal_outstanding) as total_principal_outstanding,\n -- Yield statistics\n avg(all_in_yield) as avg_yield,\n min(all_in_yield) as min_yield,\n max(all_in_yield) as max_yield,\n avg(spread_bps) as avg_spread_bps,\n min(spread_bps) as min_spread_bps,\n max(spread_bps) as max_spread_bps,\n -- Yield buckets for distribution analysis\n case\n when all_in_yield < 0.05 then '0-5%'\n when all_in_yield >= 0.05 and all_in_yield < 0.08 then '5-8%'\n when all_in_yield >= 0.08 and all_in_yield < 0.10 then '8-10%'\n when all_in_yield >= 0.10 and all_in_yield < 0.12 then '10-12%'\n when all_in_yield >= 0.12 then '12%+'\n else 'Unknown'\n end as yield_bucket\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and all_in_yield is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by security_rank, interest_index, yield_bucket\norder by security_rank, avg_yield desc","compiled":false,"inline":false},{"id":"credit_exposure_by_rank","compiledQueryString":"-- Credit Exposure by Security Rank\n-- Shows principal outstanding aggregated by security rank for credit instruments\n-- Provides visibility into portfolio risk profile and seniority distribution\n\nselect\n security_rank,\n sum(principal_outstanding) as total_principal_outstanding,\n sum(undrawn_commitment) as total_undrawn_commitment,\n sum(principal_outstanding) + sum(undrawn_commitment) as total_exposure,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies,\n count(distinct fund_id) as number_of_funds,\n -- Calculate percentage of total portfolio\n sum(principal_outstanding) / nullif(\n sum(sum(principal_outstanding)) over (), 0\n ) as pct_of_total_principal,\n -- Average metrics by rank\n avg(all_in_yield) as avg_yield,\n avg(spread_bps) as avg_spread_bps\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and security_rank is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by security_rank\norder by total_principal_outstanding desc","inputQueryString":"-- Credit Exposure by Security Rank\n-- Shows principal outstanding aggregated by security rank for credit instruments\n-- Provides visibility into portfolio risk profile and seniority distribution\n\nselect\n security_rank,\n sum(principal_outstanding) as total_principal_outstanding,\n sum(undrawn_commitment) as total_undrawn_commitment,\n sum(principal_outstanding) + sum(undrawn_commitment) as total_exposure,\n count(distinct instrument_id) as number_of_loans,\n count(distinct company_id) as number_of_companies,\n count(distinct fund_id) as number_of_funds,\n -- Calculate percentage of total portfolio\n sum(principal_outstanding) / nullif(\n sum(sum(principal_outstanding)) over (), 0\n ) as pct_of_total_principal,\n -- Average metrics by rank\n avg(all_in_yield) as avg_yield,\n avg(spread_bps) as avg_spread_bps\nfrom metrics_position_performance\nwhere instrument_type = 'CREDIT'\n and security_rank is not null\n and period_end_date = (\n select max(period_end_date) \n from metrics_position_performance\n )\ngroup by security_rank\norder by total_principal_outstanding desc","compiled":false,"inline":false},{"id":"fund_performance_overview","compiledQueryString":"select\n fund_id,\n '/funds/' || fund_id as fund_link,\n fund_name,\n fund_type,\n period_end_date,\n fund_nav,\n total_commitments,\n total_called_capital,\n unfunded_commitment,\n total_distributions,\n dpi,\n rvpi,\n tvpi,\n expected_coc,\n number_of_portfolio_companies,\n number_of_positions,\n lines_of_credit_outstanding,\n peak_outstanding_credit,\n -- Credit-specific metrics\n total_exposure,\n principal_outstanding,\n undrawn_commitment,\n interest_income,\n as_of_date\nfrom metrics_fund_performance\norder by period_end_date desc","inputQueryString":"select\n fund_id,\n '/funds/' || fund_id as fund_link,\n fund_name,\n fund_type,\n period_end_date,\n fund_nav,\n total_commitments,\n total_called_capital,\n unfunded_commitment,\n total_distributions,\n dpi,\n rvpi,\n tvpi,\n expected_coc,\n number_of_portfolio_companies,\n number_of_positions,\n lines_of_credit_outstanding,\n peak_outstanding_credit,\n -- Credit-specific metrics\n total_exposure,\n principal_outstanding,\n undrawn_commitment,\n interest_income,\n as_of_date\nfrom metrics_fund_performance\norder by period_end_date desc","compiled":false,"inline":false},{"id":"fund_performance_aggregate","compiledQueryString":"-- Aggregate fund performance metrics across all funds\n-- Returns a single row with totals for the latest common period\n-- Uses the same logic as metrics_portfolio_overview for consistency\n\nselect\n sum(total_commitments) as total_commitments,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(total_distributions) as total_distributions\nfrom metrics_fund_performance\nwhere period_end_date = (\n select max(period_end_date)\n from metrics_fund_performance\n)","inputQueryString":"-- Aggregate fund performance metrics across all funds\n-- Returns a single row with totals for the latest common period\n-- Uses the same logic as metrics_portfolio_overview for consistency\n\nselect\n sum(total_commitments) as total_commitments,\n sum(unfunded_commitment) as unfunded_commitment,\n sum(total_distributions) as total_distributions\nfrom metrics_fund_performance\nwhere period_end_date = (\n select max(period_end_date)\n from metrics_fund_performance\n)","compiled":false,"inline":false},{"id":"equity_funds_aggregate","compiledQueryString":"-- Aggregate metrics for equity funds only\nwith latest_periods as (\n select\n fund_id,\n max(period_end_date) as latest_period_end_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n group by fund_id\n)\n\nselect\n sum(mfp.total_commitments) as total_commitments,\n sum(mfp.unfunded_commitment) as unfunded_commitment,\n sum(mfp.total_distributions) as total_distributions,\n sum(mfp.fund_nav) as total_nav,\n count(distinct mfp.fund_id) as number_of_funds\nfrom metrics_fund_performance mfp\ninner join latest_periods lp\n on mfp.fund_id = lp.fund_id\n and mfp.period_end_date = lp.latest_period_end_date\nwhere mfp.fund_type = 'EQUITY'","inputQueryString":"-- Aggregate metrics for equity funds only\nwith latest_periods as (\n select\n fund_id,\n max(period_end_date) as latest_period_end_date\n from metrics_fund_performance\n where fund_type = 'EQUITY'\n group by fund_id\n)\n\nselect\n sum(mfp.total_commitments) as total_commitments,\n sum(mfp.unfunded_commitment) as unfunded_commitment,\n sum(mfp.total_distributions) as total_distributions,\n sum(mfp.fund_nav) as total_nav,\n count(distinct mfp.fund_id) as number_of_funds\nfrom metrics_fund_performance mfp\ninner join latest_periods lp\n on mfp.fund_id = lp.fund_id\n and mfp.period_end_date = lp.latest_period_end_date\nwhere mfp.fund_type = 'EQUITY'","compiled":false,"inline":false},{"id":"credit_funds_aggregate","compiledQueryString":"-- Aggregate metrics for credit funds only\nwith latest_periods as (\n select\n fund_id,\n max(period_end_date) as latest_period_end_date\n from metrics_fund_performance\n where fund_type = 'CREDIT'\n group by fund_id\n)\n\nselect\n sum(mfp.total_exposure) as total_exposure,\n sum(mfp.principal_outstanding) as principal_outstanding,\n sum(mfp.undrawn_commitment) as undrawn_commitment,\n sum(mfp.interest_income) as interest_income,\n sum(mfp.total_commitments) as total_commitments,\n count(distinct mfp.fund_id) as number_of_funds\nfrom metrics_fund_performance mfp\ninner join latest_periods lp\n on mfp.fund_id = lp.fund_id\n and mfp.period_end_date = lp.latest_period_end_date\nwhere mfp.fund_type = 'CREDIT'","inputQueryString":"-- Aggregate metrics for credit funds only\nwith latest_periods as (\n select\n fund_id,\n max(period_end_date) as latest_period_end_date\n from metrics_fund_performance\n where fund_type = 'CREDIT'\n group by fund_id\n)\n\nselect\n sum(mfp.total_exposure) as total_exposure,\n sum(mfp.principal_outstanding) as principal_outstanding,\n sum(mfp.undrawn_commitment) as undrawn_commitment,\n sum(mfp.interest_income) as interest_income,\n sum(mfp.total_commitments) as total_commitments,\n count(distinct mfp.fund_id) as number_of_funds\nfrom metrics_fund_performance mfp\ninner join latest_periods lp\n on mfp.fund_id = lp.fund_id\n and mfp.period_end_date = lp.latest_period_end_date\nwhere mfp.fund_type = 'CREDIT'","compiled":false,"inline":false},{"id":"funds_list","compiledQueryString":"select\n fund_id,\n 'portfolio/fund-' || fund_id as fund_link,\n 'analysis/reports/fund-report-' || fund_id as report_link,\n fund_name,\n fund_type,\n fund_nav,\n total_commitments,\n unfunded_commitment,\n total_distributions,\n dpi,\n rvpi,\n tvpi,\n number_of_portfolio_companies,\n number_of_positions,\n -- Credit-specific metrics\n total_exposure,\n principal_outstanding,\n undrawn_commitment,\n interest_income,\n period_end_date\nfrom metrics_fund_performance\nwhere period_end_date = (\n select max(period_end_date)\n from metrics_fund_performance mfp\n where mfp.fund_id = metrics_fund_performance.fund_id\n)\norder by fund_name","inputQueryString":"select\n fund_id,\n 'portfolio/fund-' || fund_id as fund_link,\n 'analysis/reports/fund-report-' || fund_id as report_link,\n fund_name,\n fund_type,\n fund_nav,\n total_commitments,\n unfunded_commitment,\n total_distributions,\n dpi,\n rvpi,\n tvpi,\n number_of_portfolio_companies,\n number_of_positions,\n -- Credit-specific metrics\n total_exposure,\n principal_outstanding,\n undrawn_commitment,\n interest_income,\n period_end_date\nfrom metrics_fund_performance\nwhere period_end_date = (\n select max(period_end_date)\n from metrics_fund_performance mfp\n where mfp.fund_id = metrics_fund_performance.fund_id\n)\norder by fund_name","compiled":false,"inline":false},{"id":"funds","compiledQueryString":"select distinct\n fund_id,\n fund_name\nfrom metrics_exposure_timeseries\norder by fund_name","inputQueryString":"select distinct\n fund_id,\n fund_name\nfrom metrics_exposure_timeseries\norder by fund_name","compiled":false,"inline":false}]}

Welcome to AMOS, the open-source data platform for private-market funds.

Portfolio