RWM Console cluster: risingwave-alinma.alinma-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 NOT a.account_id IS NULL
    THEN h.market_value + a.accrued_amount
    WHEN NOT h.deposit_profit_accrued IS NULL
    THEN h.market_value + h.deposit_profit_accrued
    ELSE h.market_value
  END AS fair_value,
  CASE
    WHEN NOT a.account_id IS NULL
    THEN a.accrued_amount
    WHEN NOT h.deposit_profit_accrued IS NULL
    THEN h.deposit_profit_accrued
    ELSE CAST(0 AS DECIMAL)
  END AS accrued_value,
  CASE
    WHEN NOT a.account_id IS NULL
    THEN a.accrued_value_system_currency
    WHEN NOT h.deposit_profit_accrued IS NULL
    THEN h.deposit_profit_accrued * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
    ELSE CAST(0 AS DECIMAL)
  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,
  COALESCE(
    h.total_cost_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,
  COALESCE(
    h.average_cost_per_unit_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 NOT a.account_id IS NULL
    THEN (
      h.market_value + a.accrued_amount
    ) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
    WHEN NOT h.deposit_profit_accrued IS NULL
    THEN (
      h.market_value + h.deposit_profit_accrued
    ) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
    ELSE h.market_value * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
  END AS fair_value_system_currency
FROM insights.holding_values_raw_mv 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'