RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW insights.flow_by_distribution_mv AS
SELECT
  account_group_id,
  dim_transaction_date,
  distribution_type,
  taxonomy_node_id,
  taxonomy_code,
  position_type,
  currency_code,
  adjusted_net_flow,
  adjusted_net_flow_system_currency,
  SUM(adjusted_net_flow) OVER w AS cumulative_adjusted_netflow,
  SUM(adjusted_net_flow_system_currency) OVER w AS cumulative_adjusted_netflow_system_currency
FROM (
  SELECT
    av.account_group_id,
    av.dim_transaction_date,
    ad.distribution_type,
    ad.taxonomy_node_id,
    ad.taxonomy_code,
    av.position_type,
    av.group_currency AS currency_code,
    SUM(av.net_value_group * ad.share) AS adjusted_net_flow,
    SUM(av.net_value_system * ad.share) AS adjusted_net_flow_system_currency
  FROM insights.flow_asset_values_mv AS av
  JOIN insights.asset_distributions_for_consumers_mv AS ad
    ON ad.asset_id = av.asset_id
    AND av.dim_transaction_date >= ad.effective_start_date
    AND av.dim_transaction_date < ad.effective_end_date
  GROUP BY
    av.account_group_id,
    av.dim_transaction_date,
    ad.distribution_type,
    ad.taxonomy_node_id,
    ad.taxonomy_code,
    av.position_type,
    av.group_currency
) AS grouped
WINDOW w AS (
  PARTITION BY account_group_id, distribution_type, taxonomy_node_id, position_type, currency_code
  ORDER BY dim_transaction_date
)