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

CREATE MATERIALIZED VIEW alinma_bff.party_fee_totals_mv
WITH (
  backfill_order=FIXED(olap.transaction_types_dm -> olap.transactions_dm, olap.fee_transactions_dm -> olap.transactions_dm, asset_service.foreign_exchange_rates_eod_ft -> olap.transactions_dm)
) AS
WITH party_fees AS (
  SELECT
    pa.party_id,
    pa.account_group_type,
    ft.fee_type,
    t.net_value * COALESCE(fx_party.rate, CASE WHEN t.currency_code = pa.party_currency THEN 1 ELSE NULL END) AS value_party,
    t.net_value * COALESCE(fx_system.rate, CASE WHEN t.currency_code = 'SAR' THEN 1 ELSE NULL END) AS value_system
  FROM olap.transactions_dm AS t
  JOIN olap.transaction_types_dm AS tt
    ON tt.transaction_type_id = t.transaction_type_id AND tt.type = 'FEE'
  JOIN olap.fee_transactions_dm AS ft
    ON ft.transaction_id = t.transaction_id
  JOIN alinma_bff.party_current_account_membership_mv AS pa
    ON pa.account_id = t.account_id
  LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_party
    ON fx_party.source_currency_code = t.currency_code
    AND fx_party.target_currency_code = pa.party_currency
    AND fx_party.date = t.transaction_valuation_date
  LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_system
    ON fx_system.source_currency_code = t.currency_code
    AND fx_system.target_currency_code = 'SAR'
    AND fx_system.date = t.transaction_valuation_date
  WHERE
    t.disabled_at IS NULL
)
SELECT
  party_id,
  account_group_type,
  SUM(value_party) AS total_fees,
  SUM(value_party) FILTER(WHERE
    fee_type = 'TRANSACTIONAL') AS transactional_fees,
  SUM(value_party) FILTER(WHERE
    fee_type = 'NON_TRANSACTIONAL') AS non_transactional_fees,
  SUM(value_system) AS total_fees_system_currency,
  SUM(value_system) FILTER(WHERE
    fee_type = 'TRANSACTIONAL') AS transactional_fees_system_currency,
  SUM(value_system) FILTER(WHERE
    fee_type = 'NON_TRANSACTIONAL') AS non_transactional_fees_system_currency
FROM party_fees
GROUP BY
  party_id,
  account_group_type