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

CREATE MATERIALIZED VIEW insights.position_by_distribution_mv AS
SELECT
  av.account_group_id,
  av.dim_value_date AS dim_balance_date,
  av.position_type,
  ad.distribution_type,
  ad.item_id,
  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,
  SUM(av.market_value_system * ad.share) / NULLIF(b.market_value_system_currency, 0) AS weight,
  SUM(av.fair_value_system * ad.share) / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight
FROM insights.position_asset_values_mv AS av
JOIN insights.asset_distributions_for_consumers_mv AS ad
  ON ad.asset_id = av.asset_id
  AND av.dim_value_date >= ad.effective_start_date
  AND av.dim_value_date < ad.effective_end_date
JOIN insights.position_summary_mv AS b
  ON b.account_group_id = av.account_group_id
  AND b.dim_balance_date = av.dim_value_date
  AND b.position_type = av.position_type
  AND b.currency_code = av.group_currency
GROUP BY
  av.account_group_id,
  av.dim_value_date,
  av.group_currency,
  av.position_type,
  ad.distribution_type,
  ad.item_id,
  ad.taxonomy_node_id,
  b.market_value_system_currency,
  b.fair_value_system_currency