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

CREATE MATERIALIZED VIEW adib_rm.investment_account_balance_snapshot_journal_mv
WITH (
  backfill_order=FIXED(asset_service.assets_dm_next -> olap.holding_values_intraday_ft)
) AS
WITH settled_latest AS (
  SELECT
    account_id,
    currency_code,
    settled_cash_balance,
    dim_settlement_date AS as_of_date
  FROM (
    SELECT
      account_id,
      currency_code,
      settled_cash_balance,
      dim_settlement_date,
      ROW_NUMBER() OVER (PARTITION BY account_id, currency_code ORDER BY dim_settlement_date DESC) AS rn
    FROM insights.settled_cash_balances_mv AS settled_cash_balances_mv_next
    WHERE
      dim_settlement_date <= CURRENT_TIMESTAMP
  ) AS ranked
  WHERE
    rn = 1
), intraday_latest AS (
  SELECT
    account_id,
    currency_code,
    SUM(market_value) AS market_value,
    MAX(as_of_date) AS as_of_date
  FROM (
    SELECT
      h.account_id,
      h.currency_code,
      h.market_value,
      CAST(h.holding_timestamp AS DATE) AS as_of_date,
      ROW_NUMBER() OVER (PARTITION BY h.account_id, h.asset_id ORDER BY h.holding_timestamp DESC) AS rn
    FROM olap.holding_values_intraday_ft AS h
    LEFT JOIN asset_service.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
      ON a.id = h.asset_id
    WHERE
      h.disabled_at IS NULL AND a.type = 'CASH'
  ) AS ranked
  WHERE
    rn = 1
  GROUP BY
    account_id,
    currency_code
), keys AS (
  SELECT
    account_id,
    currency_code
  FROM (
    SELECT
      account_id,
      currency_code
    FROM settled_latest
    UNION ALL
    SELECT
      account_id,
      currency_code
    FROM intraday_latest
  ) AS u
  GROUP BY
    account_id,
    currency_code
), live AS (
  SELECT
    k.account_id,
    CASE
      WHEN NOT i.account_id IS NULL
      AND (
        s.account_id IS NULL OR i.as_of_date >= s.as_of_date
      )
      THEN i.market_value
      ELSE s.settled_cash_balance
    END AS market_value,
    CASE
      WHEN NOT i.account_id IS NULL
      AND (
        s.account_id IS NULL OR i.as_of_date >= s.as_of_date
      )
      THEN i.as_of_date
      ELSE s.as_of_date
    END AS as_of_date
  FROM keys AS k
  LEFT JOIN settled_latest AS s
    ON s.account_id = k.account_id AND s.currency_code = k.currency_code
  LEFT JOIN intraday_latest AS i
    ON i.account_id = k.account_id AND i.currency_code = k.currency_code
)
SELECT
  live.account_id,
  SUM(live.market_value) AS available_balance,
  MAX(live.as_of_date) AS fact_date
FROM live
JOIN olap.accounts_dm AS a
  ON a.account_id = live.account_id AND a.disabled_at IS NULL
GROUP BY
  live.account_id