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

CREATE MATERIALIZED VIEW insights.settled_cash_series_mv
WITH (
  backfill_order=FIXED(asset_service.assets_dm -> insights.transactions_merged_mv)
) AS
WITH cash_legs AS (
  SELECT
    t.account_id,
    t.asset_id,
    t.currency_code,
    t.transaction_settlement_date AS dim_settlement_date,
    t.transaction_id,
    t.net_value
  FROM insights.transactions_merged_mv AS t
  LEFT JOIN asset_service.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
    ON a.id = t.asset_id
  WHERE
    NOT t.transaction_settlement_date IS NULL AND a.type = 'CASH'
), daily AS (
  SELECT
    account_id,
    asset_id,
    currency_code,
    dim_settlement_date,
    SUM(net_value) AS settlement_amount_delta,
    COUNT(transaction_id) AS transaction_count
  FROM cash_legs
  GROUP BY
    account_id,
    asset_id,
    currency_code,
    dim_settlement_date
)
SELECT
  account_id,
  asset_id,
  currency_code,
  dim_settlement_date,
  settlement_amount_delta,
  SUM(settlement_amount_delta) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_settlement_date) AS settled_cash_balance,
  transaction_count
FROM daily