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

CREATE MATERIALIZED VIEW insights.settled_cost_basis_carried_mv AS
WITH real_groups AS (
  SELECT
    account_id,
    asset_id,
    type,
    currency_code,
    dim_value_date,
    average_cost_per_unit,
    average_cost_per_unit_system_currency,
    total_cost_system_currency,
    cost_fx_provenance,
    purchased_quantity,
    m_is_stub,
    SUM(CASE WHEN NOT m_is_stub IS TRUE THEN 1 ELSE 0 END) OVER (
      PARTITION BY account_id, asset_id, currency_code
      ORDER BY dim_value_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS real_group
  FROM insights.settled_cost_basis_dedup_mv
)
SELECT
  account_id,
  asset_id,
  type,
  currency_code,
  dim_value_date,
  average_cost_per_unit,
  CASE
    WHEN m_is_stub IS TRUE
    THEN MAX(
      CASE
        WHEN NOT m_is_stub IS TRUE
        THEN average_cost_per_unit_system_currency
        ELSE NULL
      END
    ) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
    ELSE average_cost_per_unit_system_currency
  END AS average_cost_per_unit_system_currency,
  CASE
    WHEN m_is_stub IS TRUE
    THEN MAX(CASE WHEN NOT m_is_stub IS TRUE THEN total_cost_system_currency ELSE NULL END) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
    ELSE total_cost_system_currency
  END AS total_cost_system_currency,
  CASE
    WHEN m_is_stub IS TRUE
    THEN MAX(CASE WHEN NOT m_is_stub IS TRUE THEN cost_fx_provenance ELSE NULL END) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
    ELSE cost_fx_provenance
  END AS cost_fx_provenance,
  purchased_quantity,
  m_is_stub
FROM real_groups