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

CREATE MATERIALIZED VIEW insights.account_groups_mv AS
SELECT
  'account_group_' || MD5(CAST((
    oa.account_id || 'all'
  ) AS BYTEA)) AS account_group_id,
  oa.base_currency_code AS base_currency,
  oa.opening_date,
  'account' AS source_entity_type
FROM insights.open_accounts_mv AS oa
UNION ALL
SELECT
  'account_group_' || MD5(CAST((
    c.id || 'all'
  ) AS BYTEA)) AS account_group_id,
  c.base_currency_code AS base_currency,
  c.onboarding_date AS opening_date,
  'client' AS source_entity_type
FROM olap.clients_dm AS c
JOIN insights.client_account_group_types_mv AS cagt
  ON cagt.client_id = c.id AND cagt.type = 'all'
WHERE
  c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
  'account_group_' || MD5(CAST((
    p.portfolio_id || 'all'
  ) AS BYTEA)) AS account_group_id,
  p.base_currency_code AS base_currency,
  p.opening_date,
  'portfolio' AS source_entity_type
FROM olap.portfolios_dm AS p
JOIN insights.portfolio_account_group_types_mv AS pagt
  ON pagt.portfolio_id = p.portfolio_id AND pagt.type = 'all'
WHERE
  p.disabled_at IS NULL AND NOT p.m_is_stub IS TRUE
UNION ALL
SELECT
  'account_group_' || MD5(CAST((
    uam.user_id || uam.type
  ) AS BYTEA)) AS account_group_id,
  u.base_currency_code AS base_currency,
  MIN(oa.opening_date) AS opening_date,
  'user' AS source_entity_type
FROM insights.user_account_membership_mv AS uam
JOIN olap.users_dm AS u
  ON u.user_id = uam.user_id
JOIN insights.open_accounts_mv AS oa
  ON oa.account_id = uam.account_id
GROUP BY
  uam.user_id,
  uam.type,
  u.base_currency_code
UNION ALL
SELECT
  'account_group_' || MD5(CAST((
    pa.party_id || pa.type
  ) AS BYTEA)) AS account_group_id,
  MIN(lp.base_currency_code) AS base_currency,
  MIN(oa.opening_date) AS opening_date,
  'party' AS source_entity_type
FROM (
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    type
  FROM insights.party_account_direct_mv AS party_account_direct_mv_next
  UNION ALL
  SELECT
    party_id,
    customer_relationship_id,
    account_id,
    type
  FROM insights.party_account_via_portfolio_mv AS party_account_via_portfolio_mv_next
) AS pa
JOIN insights.open_accounts_mv AS oa
  ON oa.account_id = pa.account_id
LEFT JOIN party.lifecycle_profiles AS lp
  ON lp.customer_relationship_id = pa.customer_relationship_id
  AND lp.disabled_at IS NULL
GROUP BY
  pa.party_id,
  pa.type
HAVING
  NOT MIN(lp.base_currency_code) IS NULL