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

CREATE MATERIALIZED VIEW insights.settled_cost_basis_latest_mv 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
FROM (
  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,
    ROW_NUMBER() OVER (
      PARTITION BY account_id, asset_id
      ORDER BY (
        m_is_stub IS TRUE
      ), dim_value_date DESC, type
    ) AS rn
  FROM insights.settled_cost_basis_carried_mv AS settled_cost_basis_carried_mv_next
) AS ranked
WHERE
  rn = 1