RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW alpheya_experience_bff.party_aum_mv AS
WITH investment_accounts AS (
  SELECT
    pag.party_id,
    pag.account_group_id,
    pag.type AS account_group_type,
    pl.party_currency,
    atag.account_id
  FROM insights.party_to_account_groups_mv AS pag
  JOIN (
    SELECT
      acr.party_id,
      MIN(lp.base_currency_code) AS party_currency
    FROM alpheya_experience_bff.party_active_customer_relationships_mv AS acr
    JOIN party.lifecycle_profiles AS lp
      ON lp.customer_relationship_id = acr.customer_relationship_id
      AND lp.disabled_at IS NULL
    GROUP BY
      acr.party_id
  ) AS pl
    ON pl.party_id = pag.party_id
  JOIN insights.account_to_account_groups_mv AS atag
    ON atag.account_group_id = pag.account_group_id AND atag.effective_end_date IS NULL
), holdings_by_currency AS (
  SELECT
    ia.party_id,
    ia.account_group_type,
    ia.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 investment_accounts AS ia
  JOIN alpheya_experience_bff.investment_holdings_latest_mv AS h
    ON h.account_id = ia.account_id
  JOIN insights.position_snapshot_mv AS lab
    ON lab.account_group_id = ia.account_group_id
    AND lab.position_type = 'POSITION'
    AND lab.currency_code = ia.party_currency
  GROUP BY
    ia.party_id,
    ia.account_group_type,
    ia.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 ELSE NULL END)
  ) AS aum_market_value,
  SUM(
    hbc.fair_value * COALESCE(fx.rate, CASE WHEN hbc.currency_code = hbc.party_currency THEN 1 ELSE NULL 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
WHERE
  NOT fx.rate IS NULL OR hbc.currency_code = hbc.party_currency
GROUP BY
  hbc.party_id,
  hbc.account_group_type