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

CREATE MATERIALIZED VIEW insights.private_asset_valuations_mv AS
SELECT
  v.asset_id,
  v.fact_date,
  v.market_value_currency,
  v.market_value * COALESCE(fxm.rate, CASE WHEN v.market_value_currency = 'AED' THEN 1 ELSE NULL END) AS value_system_currency,
  CASE
    WHEN COALESCE(v.average_cost, 0) = 0
    THEN 0
    ELSE v.average_cost * COALESCE(
      fxc.rate,
      CASE
        WHEN COALESCE(v.average_cost_currency, v.market_value_currency) = 'AED'
        THEN 1
        ELSE NULL
      END
    )
  END AS cost_system_currency
FROM olap.private_asset_valuations_ft AS v
LEFT JOIN insights.fx_rates_snapshot_mv AS fxm
  ON fxm.source_currency_code = v.market_value_currency
  AND fxm.target_currency_code = 'AED'
LEFT JOIN insights.fx_rates_snapshot_mv AS fxc
  ON fxc.source_currency_code = COALESCE(v.average_cost_currency, v.market_value_currency)
  AND fxc.target_currency_code = 'AED'
WHERE
  v.disabled_at IS NULL
  AND (
    NOT fxm.rate IS NULL OR v.market_value_currency = 'AED'
  )
  AND (
    NOT fxc.rate IS NULL
    OR COALESCE(v.average_cost_currency, v.market_value_currency) = 'AED'
    OR COALESCE(v.average_cost, 0) = 0
  )