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

CREATE MATERIALIZED VIEW alpheya_experience_bff.party_fee_totals_mv AS
SELECT
  pag.party_id,
  pag.type AS account_group_type,
  SUM(fv.net_value_group) AS total_fees,
  SUM(CASE WHEN ft.fee_type = 'TRANSACTIONAL' THEN fv.net_value_group ELSE 0 END) AS transactional_fees,
  SUM(CASE WHEN ft.fee_type = 'NON_TRANSACTIONAL' THEN fv.net_value_group ELSE 0 END) AS non_transactional_fees,
  SUM(fv.net_value_system) AS total_fees_system_currency,
  SUM(CASE WHEN ft.fee_type = 'TRANSACTIONAL' THEN fv.net_value_system ELSE 0 END) AS transactional_fees_system_currency,
  SUM(CASE WHEN ft.fee_type = 'NON_TRANSACTIONAL' THEN fv.net_value_system ELSE 0 END) AS non_transactional_fees_system_currency
FROM (
  SELECT
    transaction_id,
    account_group_id,
    net_value_group,
    net_value_system
  FROM insights.flow_values_mv AS flow_values_mv_next
  WHERE
    transaction_type = 'FEE' AND position_type = 'POSITION'
) AS fv
JOIN olap.fee_transactions_dm AS ft
  ON ft.transaction_id = fv.transaction_id
JOIN insights.party_to_account_groups_mv AS pag
  ON pag.account_group_id = fv.account_group_id
JOIN alpheya_experience_bff.party_active_customer_parties_mv AS acp
  ON acp.party_id = pag.party_id
GROUP BY
  pag.party_id,
  pag.type