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

CREATE MATERIALIZED VIEW insights.intraday_position_by_distribution_mv AS
WITH by_distribution AS (
  SELECT
    av.account_group_id,
    av.position_type,
    ad.distribution_type,
    ad.taxonomy_node_id,
    av.group_currency AS currency_code,
    SUM(av.market_value * ad.share) AS market_value,
    SUM(av.average_cost * ad.share) AS total_average_cost,
    SUM(av.fair_value * ad.share) AS fair_value,
    SUM(av.accrued_value * ad.share) AS accrued_value,
    SUM(av.market_value_system * ad.share) AS market_value_system_currency,
    SUM(av.average_cost_system * ad.share) AS total_average_cost_system_currency,
    SUM(av.fair_value_system * ad.share) AS fair_value_system_currency,
    SUM(av.accrued_value_system * ad.share) AS accrued_value_system_currency,
    MAX(av.change_time) AS change_time
  FROM insights.intraday_position_asset_values_mv AS av
  JOIN insights.asset_distributions_for_consumers_mv AS ad
    ON ad.asset_id = av.asset_id
    AND CAST(av.change_time AS DATE) >= ad.effective_start_date
    AND CAST(av.change_time AS DATE) < ad.effective_end_date
  GROUP BY
    av.account_group_id,
    av.group_currency,
    av.position_type,
    ad.distribution_type,
    ad.taxonomy_node_id
)
SELECT
  d.account_group_id,
  d.position_type,
  d.distribution_type,
  d.taxonomy_node_id,
  d.currency_code,
  d.market_value,
  d.total_average_cost,
  d.fair_value,
  d.accrued_value,
  d.market_value_system_currency,
  d.total_average_cost_system_currency,
  d.fair_value_system_currency,
  d.accrued_value_system_currency,
  d.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
  d.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight,
  d.change_time
FROM by_distribution AS d
JOIN insights.intraday_position_summary_mv AS b
  ON b.account_group_id = d.account_group_id
  AND b.position_type = d.position_type
  AND b.currency_code = d.currency_code