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

CREATE MATERIALIZED VIEW insights.position_by_currency_mv AS
WITH agg AS (
  SELECT
    hv.account_group_id,
    hv.dim_value_date AS dim_balance_date,
    COALESCE(hv.position_type, 'POSITION') AS position_type,
    hv.holding_currency AS item_id,
    hv.group_currency AS currency_code,
    SUM(hv.market_value_group) AS market_value,
    SUM(hv.average_cost_group) AS total_average_cost,
    SUM(hv.fair_value_group) AS fair_value,
    SUM(hv.accrued_value_group) AS accrued_value,
    SUM(hv.market_value_system) AS market_value_system_currency,
    SUM(hv.average_cost_system) AS total_average_cost_system_currency,
    SUM(hv.fair_value_system) AS fair_value_system_currency,
    SUM(hv.accrued_value_system) AS accrued_value_system_currency
  FROM insights.position_values_mv AS hv
  GROUP BY
    GROUPING SETS (
      (
        hv.account_group_id,
        hv.dim_value_date,
        hv.position_type,
        hv.holding_currency,
        hv.group_currency
      ),
      (hv.account_group_id, hv.dim_value_date, hv.holding_currency, hv.group_currency)
    )
)
SELECT
  p.account_group_id,
  p.dim_balance_date,
  p.position_type,
  p.item_id,
  p.currency_code,
  p.market_value,
  p.total_average_cost,
  p.fair_value,
  p.accrued_value,
  p.market_value_system_currency,
  p.total_average_cost_system_currency,
  p.fair_value_system_currency,
  p.accrued_value_system_currency,
  p.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
  p.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight
FROM agg AS p
JOIN insights.position_summary_mv AS b
  ON b.account_group_id = p.account_group_id
  AND b.dim_balance_date = p.dim_balance_date
  AND b.position_type = p.position_type
  AND b.currency_code = p.currency_code