RWM Console cluster: risingwave-alinma.alinma-rw.svc.cluster.local

CREATE MATERIALIZED VIEW alinma_bff.party_aum_mv AS
WITH holdings_by_currency AS (
  SELECT
    pa.party_id,
    pa.account_group_type,
    pa.party_currency,
    h.currency_code,
    SUM(h.market_value) AS market_value,
    SUM(h.fair_value) AS fair_value,
    SUM(h.market_value_system_currency) AS market_value_system_currency,
    SUM(h.fair_value_system_currency) AS fair_value_system_currency
  FROM alinma_bff.party_current_account_membership_mv AS pa
  JOIN alinma_bff.investment_holdings_latest_mv AS h
    ON h.account_id = pa.account_id
  GROUP BY
    pa.party_id,
    pa.account_group_type,
    pa.party_currency,
    h.currency_code
)
SELECT
  hbc.party_id,
  hbc.account_group_type,
  SUM(
    hbc.market_value * COALESCE(fx.rate, CASE WHEN hbc.currency_code = hbc.party_currency THEN 1 END)
  ) AS aum_market_value,
  SUM(
    hbc.fair_value * COALESCE(fx.rate, CASE WHEN hbc.currency_code = hbc.party_currency THEN 1 END)
  ) AS fair_aum_market_value,
  SUM(hbc.market_value_system_currency) AS aum_market_value_system_currency,
  SUM(hbc.fair_value_system_currency) AS fair_aum_market_value_system_currency
FROM holdings_by_currency AS hbc
LEFT JOIN insights.fx_rates_snapshot_mv AS fx
  ON fx.source_currency_code = hbc.currency_code
  AND fx.target_currency_code = hbc.party_currency
GROUP BY
  hbc.party_id,
  hbc.account_group_type