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

CREATE MATERIALIZED VIEW insights.settled_position_valuations_mv AS
WITH latest_position AS (
  SELECT
    account_id,
    asset_id,
    currency_code,
    dim_settlement_date,
    settled_quantity,
    settled_value
  FROM (
    SELECT
      account_id,
      asset_id,
      currency_code,
      dim_settlement_date,
      settled_quantity,
      settled_value,
      ROW_NUMBER() OVER (
        PARTITION BY account_id, asset_id, currency_code
        ORDER BY dim_settlement_date DESC
      ) AS rn
    FROM insights.settled_position_series_mv
    WHERE
      dim_settlement_date <= CURRENT_TIMESTAMP
  ) AS ranked
  WHERE
    rn = 1
)
SELECT
  p.account_id,
  p.asset_id,
  p.currency_code,
  p.dim_settlement_date AS last_settlement_date,
  p.settled_quantity,
  p.settled_value,
  px.price,
  px.price_date,
  p.settled_quantity * px.price AS market_value,
  p.settled_quantity * px.price * COALESCE(fx.rate, CASE WHEN p.currency_code = 'SAR' THEN 1 ELSE NULL END) AS market_value_system_currency,
  cb.average_cost_per_unit,
  cb.average_cost_per_unit_system_currency,
  cb.purchased_quantity AS baseline_purchased_quantity,
  cb.dim_value_date AS cost_basis_date,
  cb.m_is_stub AS cost_basis_is_stub,
  p.settled_quantity * cb.average_cost_per_unit AS total_cost,
  cb.total_cost_system_currency,
  cb.cost_fx_provenance
FROM latest_position AS p
LEFT JOIN insights.settled_cost_basis_latest_mv AS cb
  ON cb.account_id = p.account_id AND cb.asset_id = p.asset_id
LEFT JOIN insights.asset_latest_price_mv AS px
  ON px.asset_id = p.asset_id
LEFT JOIN insights.fx_rates_snapshot_mv AS fx
  ON fx.source_currency_code = p.currency_code AND fx.target_currency_code = 'SAR'