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

CREATE MATERIALIZED VIEW adib_rm.party_cash_tiles_mv
WITH (
  backfill_order=FIXED(asset_service.currencies_dm -> insights.party_account_direct_mv_next)
) AS
SELECT
  pad.party_id,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'accountId',
      a.account_id,
      'name',
      a.name,
      'number',
      a.number,
      'value',
      JSONB_BUILD_OBJECT(
        'amount',
        CAST(iabs.available_balance AS VARCHAR),
        'currency',
        JSONB_BUILD_OBJECT('code', a.base_currency_code, 'symbol', cur.symbol)
      ),
      'baseCurrency',
      JSONB_BUILD_OBJECT('code', a.base_currency_code, 'symbol', cur.symbol)
    )
  ) AS cash_tiles
FROM insights.party_account_direct_mv AS pad
JOIN adib_rm.accounts_plain_mv AS a
  ON a.account_id = pad.account_id
LEFT JOIN asset_service.currencies_dm FOR SYSTEM_TIME AS OF PROCTIME() AS cur
  ON cur.code = a.base_currency_code
LEFT JOIN adib_rm.investment_account_balance_snapshot_journal_mv AS iabs
  ON iabs.account_id = a.account_id
WHERE
  pad.type = 'all'
  AND pad.effective_start_date <= CURRENT_TIMESTAMP
  AND (
    pad.effective_end_date IS NULL OR pad.effective_end_date > CURRENT_TIMESTAMP
  )
  AND a.product_type_id IN (
    SELECT
      product_type_id
    FROM adib_rm.cash_product_type_ids_mv
  )
GROUP BY
  pad.party_id