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

CREATE MATERIALIZED VIEW insights.user_account_membership_mv AS
WITH user_account_paths AS (
  SELECT
    up.user_id,
    oa.account_id,
    oa.is_restricted
  FROM insights.user_to_portfolios_mv AS up
  JOIN olap.account_to_portfolios_dm AS atp
    ON atp.portfolio_id = up.portfolio_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  WHERE
    atp.disabled_at IS NULL
  UNION ALL
  SELECT
    uc.user_id,
    oa.account_id,
    oa.is_restricted
  FROM insights.user_to_clients_mv AS uc
  JOIN olap.accounts_to_clients_dm AS atc
    ON atc.client_id = uc.client_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atc.account_id
  WHERE
    atc.disabled_at IS NULL
  UNION ALL
  SELECT
    uc.user_id,
    oa.account_id,
    oa.is_restricted
  FROM insights.user_to_clients_mv AS uc
  JOIN olap.clients_portfolios_dm AS cp
    ON cp.client_id = uc.client_id
  JOIN olap.account_to_portfolios_dm AS atp
    ON atp.portfolio_id = cp.portfolio_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  WHERE
    cp.disabled_at IS NULL AND atp.disabled_at IS NULL
), deduped AS (
  SELECT
    user_id,
    account_id,
    BOOL_OR(is_restricted) AS is_restricted
  FROM user_account_paths
  GROUP BY
    user_id,
    account_id
), active_user_accounts AS (
  SELECT
    d.user_id,
    d.account_id,
    d.is_restricted
  FROM deduped AS d
  JOIN olap.users_dm AS u
    ON u.user_id = d.user_id
  WHERE
    u.disabled_at IS NULL
)
SELECT
  user_id,
  account_id,
  CAST('all' AS VARCHAR) AS type
FROM active_user_accounts
UNION ALL
SELECT
  user_id,
  account_id,
  CAST('restricted' AS VARCHAR) AS type
FROM active_user_accounts
WHERE
  is_restricted = TRUE
UNION ALL
SELECT
  user_id,
  account_id,
  CAST('un_restricted' AS VARCHAR) AS type
FROM active_user_accounts
WHERE
  NOT is_restricted IS TRUE