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

CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_counts_mv AS
WITH user_clients AS (
  SELECT
    user_id,
    client_id
  FROM alinma_bff.advisor_kpi_user_clients_mv
), user_portfolios AS (
  SELECT
    user_id,
    portfolio_id
  FROM alinma_bff.advisor_kpi_user_portfolios_mv AS advisor_kpi_user_portfolios_mv_next
), user_accounts AS (
  SELECT
    user_id,
    account_id
  FROM alinma_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