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

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