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

CREATE MATERIALIZED VIEW insights.party_to_account_groups_mv AS
WITH active_customer_parties AS (
  SELECT
    p.id AS party_id
  FROM party.parties AS p
  JOIN (
    SELECT
      party_id
    FROM party.customer_relationships
    WHERE
      type = 'CUSTOMER' AND status = 'ACTIVE' AND disabled_at IS NULL
    GROUP BY
      party_id
  ) AS cr
    ON cr.party_id = p.id
  WHERE
    p.disabled_at IS NULL
)
SELECT
  party_id,
  'account_group_' || MD5(CAST((
    party_id || 'none'
  ) AS BYTEA)) AS account_group_id,
  CAST('none' AS VARCHAR) AS type
FROM active_customer_parties
UNION ALL
SELECT
  acp.party_id,
  'account_group_' || MD5(CAST((
    acp.party_id || 'all'
  ) AS BYTEA)) AS account_group_id,
  CAST('all' AS VARCHAR) AS type
FROM active_customer_parties AS acp
JOIN insights.party_account_group_types_mv AS pagt
  ON pagt.party_id = acp.party_id AND pagt.type = 'all'
UNION ALL
SELECT
  acp.party_id,
  'account_group_' || MD5(CAST((
    acp.party_id || 'restricted'
  ) AS BYTEA)) AS account_group_id,
  CAST('restricted' AS VARCHAR) AS type
FROM active_customer_parties AS acp
JOIN insights.party_account_group_types_mv AS pagt
  ON pagt.party_id = acp.party_id AND pagt.type = 'restricted'
UNION ALL
SELECT
  acp.party_id,
  'account_group_' || MD5(CAST((
    acp.party_id || 'un_restricted'
  ) AS BYTEA)) AS account_group_id,
  CAST('un_restricted' AS VARCHAR) AS type
FROM active_customer_parties AS acp
JOIN insights.party_account_group_types_mv AS pagt
  ON pagt.party_id = acp.party_id AND pagt.type = 'un_restricted'