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

CREATE MATERIALIZED VIEW adib_rm.client_cash_tiles_mv
WITH (
  backfill_order=FIXED(olap.currencies_dm -> olap.clients_dm)
) AS
SELECT
  c.id AS client_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 olap.clients_dm AS c
JOIN olap.accounts_to_clients_dm AS ac
  ON ac.client_id = c.id
  AND ac.disabled_at IS NULL
  AND ac.effective_start_date <= CURRENT_TIMESTAMP
  AND ac.effective_end_date IS NULL
JOIN adib_rm.accounts_plain_mv AS a
  ON a.account_id = ac.account_id
LEFT JOIN olap.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_mv AS iabs
  ON iabs.account_id = a.account_id
WHERE
  c.disabled_at IS NULL
  AND a.product_type_id IN (
    ID_FROM_STRING_WITH_PREFIX('product_type', 'INVESTMENT'),
    ID_FROM_STRING_WITH_PREFIX('product_type', 'INVESTMENT_CASH')
  )
GROUP BY
  c.id