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

CREATE MATERIALIZED VIEW insights.holding_values_with_accruals_mv AS
SELECT
  h.account_id,
  h.asset_id,
  h.dim_value_date,
  h.type,
  h.currency_code,
  h.market_value,
  h.average_cost_per_unit * h.purchased_quantity AS average_cost,
  h.average_cost_per_unit,
  h.purchased_quantity,
  CASE
    WHEN a.account_id IS NULL
    THEN h.market_value
    ELSE h.market_value + a.accrued_amount
  END AS fair_value,
  CASE WHEN a.account_id IS NULL THEN CAST(0 AS DECIMAL) ELSE a.accrued_amount END AS accrued_value,
  CASE
    WHEN a.account_id IS NULL
    THEN CAST(0 AS DECIMAL)
    ELSE a.accrued_value_system_currency
  END AS accrued_value_system_currency,
  h.market_value * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END) AS market_value_system_currency,
  (
    h.average_cost_per_unit * h.purchased_quantity
  ) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END) AS average_cost_system_currency,
  h.average_cost_per_unit * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END) AS average_cost_per_unit_system_currency,
  CASE
    WHEN a.account_id IS NULL
    THEN h.market_value * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
    ELSE (
      h.market_value + a.accrued_amount
    ) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
  END AS fair_value_system_currency
FROM olap.holding_values_raw_ft AS h
LEFT JOIN insights.accruals_agg_mv AS a
  ON a.account_id = h.account_id
  AND a.asset_id = h.asset_id
  AND a.dim_value_date = h.dim_value_date
  AND a.type = h.type
  AND a.currency_code = h.currency_code
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_sys
  ON fx_sys.source_currency_code = h.currency_code
  AND fx_sys.target_currency_code = 'SAR'
  AND fx_sys.date = h.dim_value_date
WHERE
  NOT fx_sys.rate IS NULL OR h.currency_code = 'SAR'