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

CREATE MATERIALIZED VIEW insights.settled_position_series_mv
WITH (
  backfill_order=FIXED(asset_service.assets_dm -> insights.transactions_merged_mv)
) AS
WITH security_legs AS (
  SELECT
    t.account_id,
    t.asset_id,
    t.currency_code,
    t.transaction_settlement_date AS dim_settlement_date,
    t.transaction_id,
    COALESCE(t.quantity, CAST(0 AS DECIMAL)) AS settlement_quantity_delta,
    t.net_value AS settlement_value_delta
  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 COALESCE(a.type, 'UNSPECIFIED') <> 'CASH'
), daily AS (
  SELECT
    account_id,
    asset_id,
    currency_code,
    dim_settlement_date,
    SUM(settlement_quantity_delta) AS settlement_quantity_delta,
    SUM(settlement_value_delta) AS settlement_value_delta,
    COUNT(transaction_id) AS transaction_count
  FROM security_legs
  GROUP BY
    account_id,
    asset_id,
    currency_code,
    dim_settlement_date
)
SELECT
  account_id,
  asset_id,
  currency_code,
  dim_settlement_date,
  settlement_quantity_delta,
  settlement_value_delta,
  SUM(settlement_quantity_delta) OVER w AS settled_quantity,
  SUM(settlement_value_delta) OVER w AS settled_value,
  transaction_count
FROM daily
WINDOW w AS (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_settlement_date)