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

CREATE MATERIALIZED VIEW insights.position_snapshot_mv AS
WITH converted AS (
  SELECT
    atag.account_group_id,
    atag.base_currency,
    h.type AS position_type,
    h.dim_value_date,
    h.market_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS market_value,
    h.fair_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_value,
    h.accrued_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS accrued_value,
    ABS(h.market_value) * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS relationship_value,
    ABS(h.fair_value) * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_relationship_value,
    h.market_value_system_currency,
    h.fair_value_system_currency,
    h.accrued_value_system_currency,
    ABS(h.market_value_system_currency) AS relationship_value_system_currency,
    ABS(h.fair_value_system_currency) AS fair_relationship_value_system_currency
  FROM insights.holding_values_latest_mv AS h
  JOIN insights.account_to_account_groups_mv AS atag
    ON atag.account_id = h.account_id
    AND atag.effective_end_date IS NULL
    AND atag.source_entity_type <> 'user'
  LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx
    ON fx.source_currency_code = h.currency_code
    AND fx.target_currency_code = atag.base_currency
    AND fx.date = h.dim_value_date
  WHERE
    NOT fx.rate IS NULL OR h.currency_code = atag.base_currency
)
SELECT
  account_group_id,
  MAX(dim_value_date) AS dim_balance_date,
  COALESCE(position_type, 'POSITION') AS position_type,
  base_currency AS currency_code,
  SUM(market_value) AS market_value,
  SUM(fair_value) AS fair_value,
  SUM(accrued_value) AS accrued_value,
  SUM(market_value_system_currency) AS market_value_system_currency,
  SUM(fair_value_system_currency) AS fair_value_system_currency,
  SUM(accrued_value_system_currency) AS accrued_value_system_currency,
  SUM(relationship_value) AS relationship_value,
  SUM(fair_relationship_value) AS fair_relationship_value,
  SUM(relationship_value_system_currency) AS relationship_value_system_currency,
  SUM(fair_relationship_value_system_currency) AS fair_relationship_value_system_currency
FROM converted
GROUP BY
  GROUPING SETS (
    (account_group_id, base_currency, position_type),
    (account_group_id, base_currency)
  )