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

CREATE MATERIALIZED VIEW insights.accruals_agg_mv AS
SELECT
  a.account_id,
  a.asset_id,
  a.fact_date AS dim_value_date,
  COALESCE(h.currency_code, a.currency) AS currency_code,
  CASE WHEN a.type = 'EXPENSE' THEN 'LIABILITY' ELSE 'ASSET' END AS type,
  SUM(
    a.amount * COALESCE(
      fx_accrual.rate,
      CASE WHEN a.currency = COALESCE(h.currency_code, a.currency) THEN 1 ELSE NULL END
    )
  ) AS accrued_amount,
  SUM(
    a.amount * COALESCE(fx_sys.rate, CASE WHEN a.currency = 'USD' THEN 1 ELSE NULL END)
  ) AS accrued_value_system_currency
FROM olap.accruals_ft 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.fact_date
  AND h.type = CASE WHEN a.type = 'EXPENSE' THEN 'LIABILITY' ELSE 'ASSET' END
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_accrual
  ON fx_accrual.source_currency_code = a.currency
  AND fx_accrual.target_currency_code = COALESCE(h.currency_code, a.currency)
  AND fx_accrual.date = a.fact_date
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_sys
  ON fx_sys.source_currency_code = a.currency
  AND fx_sys.target_currency_code = 'USD'
  AND fx_sys.date = a.fact_date
WHERE
  NOT a.is_included
  AND (
    NOT fx_accrual.rate IS NULL OR a.currency = COALESCE(h.currency_code, a.currency)
  )
  AND (
    NOT fx_sys.rate IS NULL OR a.currency = 'USD'
  )
GROUP BY
  a.account_id,
  a.asset_id,
  a.fact_date,
  COALESCE(h.currency_code, a.currency),
  CASE WHEN a.type = 'EXPENSE' THEN 'LIABILITY' ELSE 'ASSET' END