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

CREATE MATERIALIZED VIEW alpheya_experience_bff.client_aum_mv AS
WITH investment_accounts AS (
  SELECT
    cag.client_id,
    cag.account_group_id,
    cag.type AS account_group_type,
    c.base_currency_code AS client_currency,
    atag.account_id
  FROM insights.client_to_account_groups_mv AS cag
  JOIN olap.clients_dm AS c
    ON c.id = cag.client_id AND c.closing_date IS NULL
  JOIN insights.account_to_account_groups_mv AS atag
    ON atag.account_group_id = cag.account_group_id AND atag.effective_end_date IS NULL
), holdings_by_currency AS (
  SELECT
    ia.client_id,
    ia.account_group_type,
    ia.client_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.client_currency
  GROUP BY
    ia.client_id,
    ia.account_group_type,
    ia.client_currency,
    h.currency_code
)
SELECT
  hbc.client_id,
  hbc.account_group_type,
  SUM(
    hbc.market_value * COALESCE(fx.rate, CASE WHEN hbc.currency_code = hbc.client_currency THEN 1 ELSE NULL END)
  ) AS aum_market_value,
  SUM(
    hbc.fair_value * COALESCE(fx.rate, CASE WHEN hbc.currency_code = hbc.client_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.client_currency
WHERE
  NOT fx.rate IS NULL OR hbc.currency_code = hbc.client_currency
GROUP BY
  hbc.client_id,
  hbc.account_group_type