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

CREATE MATERIALIZED VIEW insights.flow_by_currency_mv AS
SELECT
  account_group_id,
  dim_transaction_date,
  holding_currency_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
    account_group_id,
    dim_transaction_date,
    transaction_currency AS holding_currency_code,
    CASE WHEN GROUPING(position_type) = 1 THEN 'POSITION' ELSE position_type END AS position_type,
    group_currency AS currency_code,
    SUM(net_value_group) AS adjusted_net_flow,
    SUM(net_value_system) AS adjusted_net_flow_system_currency
  FROM insights.flow_values_mv
  GROUP BY
    GROUPING SETS (
      (
        account_group_id,
        dim_transaction_date,
        transaction_currency,
        position_type,
        group_currency
      ),
      (account_group_id, dim_transaction_date, transaction_currency, group_currency)
    )
  HAVING
    GROUPING(position_type) = 1 OR NOT position_type IS NULL
) AS grouped
WINDOW w AS (
  PARTITION BY account_group_id, holding_currency_code, position_type
  ORDER BY dim_transaction_date
)