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

CREATE MATERIALIZED VIEW adib_rm.investment_account_balance_snapshot_mv AS
WITH settled_latest AS (
  SELECT
    account_id,
    asset_id,
    market_value,
    dim_value_date AS as_of_date
  FROM (
    SELECT
      account_id,
      asset_id,
      market_value,
      dim_value_date,
      ROW_NUMBER() OVER (
        PARTITION BY account_id, asset_id
        ORDER BY (
          m_is_stub IS TRUE
        ), dim_value_date DESC, type
      ) AS rn
    FROM olap.holding_values_raw_ft
  ) AS ranked
  WHERE
    rn = 1
), intraday_latest AS (
  SELECT
    account_id,
    asset_id,
    market_value,
    as_of_date
  FROM (
    SELECT
      account_id,
      asset_id,
      market_value,
      CAST(holding_timestamp AS DATE) AS as_of_date,
      ROW_NUMBER() OVER (PARTITION BY account_id, asset_id ORDER BY holding_timestamp DESC) AS rn
    FROM olap.holding_values_intraday_ft
    WHERE
      disabled_at IS NULL
  ) AS ranked
  WHERE
    rn = 1
), live AS (
  SELECT
    s.account_id,
    CASE
      WHEN NOT i.account_id IS NULL AND i.as_of_date >= s.as_of_date
      THEN i.market_value
      ELSE s.market_value
    END AS market_value,
    CASE
      WHEN NOT i.account_id IS NULL AND i.as_of_date >= s.as_of_date
      THEN i.as_of_date
      ELSE s.as_of_date
    END AS as_of_date
  FROM settled_latest AS s
  LEFT JOIN intraday_latest AS i
    ON i.account_id = s.account_id AND i.asset_id = s.asset_id
)
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