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

CREATE MATERIALIZED VIEW insights.intraday_position_by_currency_mv AS
WITH agg AS (
  SELECT
    pv.account_group_id,
    pv.position_type,
    pv.holding_currency AS item_id,
    pv.group_currency AS currency_code,
    SUM(pv.market_value_group) AS market_value,
    SUM(pv.average_cost_group) AS total_average_cost,
    SUM(pv.fair_value_group) AS fair_value,
    SUM(pv.accrued_value_group) AS accrued_value,
    SUM(pv.market_value_system) AS market_value_system_currency,
    SUM(pv.average_cost_system) AS total_average_cost_system_currency,
    SUM(pv.fair_value_system) AS fair_value_system_currency,
    SUM(pv.accrued_value_system) AS accrued_value_system_currency,
    MAX(pv.change_time) AS change_time
  FROM insights.intraday_position_values_mv AS pv
  GROUP BY
    pv.account_group_id,
    pv.position_type,
    pv.holding_currency,
    pv.group_currency
)
SELECT
  a.account_group_id,
  a.position_type,
  a.item_id,
  a.currency_code,
  a.market_value,
  a.total_average_cost,
  a.fair_value,
  a.accrued_value,
  a.market_value_system_currency,
  a.total_average_cost_system_currency,
  a.fair_value_system_currency,
  a.accrued_value_system_currency,
  a.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
  a.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight,
  a.change_time
FROM agg AS a
JOIN insights.intraday_position_summary_mv AS b
  ON b.account_group_id = a.account_group_id
  AND b.position_type = a.position_type
  AND b.currency_code = a.currency_code