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

CREATE MATERIALIZED VIEW alpheya_experience_bff.advisor_kpi_counts_mv AS
WITH user_clients AS (
  SELECT
    uc.user_id,
    uc.client_id
  FROM (
    SELECT
      utm.user_id,
      ttc.client_id
    FROM authz.active_teams_memberships_mv AS utm
    JOIN authz.team_to_clients_mv AS ttc
      ON ttc.team_id = utm.team_id
    WHERE
      utm.access_type = 'DIRECT'
  ) AS uc
  GROUP BY
    uc.user_id,
    uc.client_id
), user_portfolios AS (
  SELECT
    up.user_id,
    up.portfolio_id
  FROM (
    SELECT
      utm.user_id,
      ttp.portfolio_id
    FROM authz.active_teams_memberships_mv AS utm
    JOIN authz.team_to_portfolios_mv AS ttp
      ON ttp.team_id = utm.team_id
    WHERE
      utm.access_type = 'DIRECT'
  ) AS up
  GROUP BY
    up.user_id,
    up.portfolio_id
), user_accounts AS (
  SELECT
    user_id,
    account_id
  FROM alpheya_experience_bff.advisor_kpi_user_accounts_mv AS advisor_kpi_user_accounts_mv_next
), all_user_ids AS (
  SELECT
    user_id
  FROM user_clients
  UNION ALL
  SELECT
    user_id
  FROM user_portfolios
  UNION ALL
  SELECT
    user_id
  FROM user_accounts
), all_users AS (
  SELECT
    user_id
  FROM all_user_ids
  GROUP BY
    user_id
), client_counts AS (
  SELECT
    uac.user_id,
    CAST(COUNT(*) AS INT) AS clients_count
  FROM user_clients AS uac
  JOIN olap.clients_dm AS c
    ON c.id = uac.client_id AND c.closing_date IS NULL
  GROUP BY
    uac.user_id
), portfolio_counts AS (
  SELECT
    uap.user_id,
    CAST(COUNT(*) AS INT) AS portfolios_count
  FROM user_portfolios AS uap
  JOIN olap.portfolios_dm AS p
    ON p.portfolio_id = uap.portfolio_id AND p.disabled_at IS NULL
  GROUP BY
    uap.user_id
), account_counts AS (
  SELECT
    uaa.user_id,
    CAST(COUNT(*) AS INT) AS accounts_count
  FROM user_accounts AS uaa
  JOIN olap.accounts_dm AS a
    ON a.account_id = uaa.account_id AND a.closing_date IS NULL AND a.disabled_at IS NULL
  GROUP BY
    uaa.user_id
)
SELECT
  au.user_id,
  COALESCE(cc.clients_count, 0) AS clients_count,
  COALESCE(pc.portfolios_count, 0) AS portfolios_count,
  COALESCE(ac.accounts_count, 0) AS accounts_count
FROM all_users AS au
LEFT JOIN client_counts AS cc
  ON cc.user_id = au.user_id
LEFT JOIN portfolio_counts AS pc
  ON pc.user_id = au.user_id
LEFT JOIN account_counts AS ac
  ON ac.user_id = au.user_id