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

CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_user_bucket_adds_mv AS
WITH combined AS (
  SELECT
    up.user_id,
    a.period_kind,
    a.period_start,
    1 AS portfolios_added,
    0 AS clients_added,
    0 AS accounts_added
  FROM alinma_bff.advisor_kpi_user_portfolios_mv AS up
  JOIN alinma_bff.advisor_kpi_entity_bucket_adds_mv AS a
    ON a.entity_type = 'PORTFOLIO' AND a.entity_id = up.portfolio_id
  UNION ALL
  SELECT
    uc.user_id,
    a.period_kind,
    a.period_start,
    0,
    1,
    0
  FROM alinma_bff.advisor_kpi_user_clients_mv AS uc
  JOIN alinma_bff.advisor_kpi_entity_bucket_adds_mv AS a
    ON a.entity_type = 'CLIENT' AND a.entity_id = uc.client_id
  UNION ALL
  SELECT
    ua.user_id,
    a.period_kind,
    a.period_start,
    0,
    0,
    1
  FROM alinma_bff.advisor_kpi_user_accounts_mv AS ua
  JOIN alinma_bff.advisor_kpi_entity_bucket_adds_mv AS a
    ON a.entity_type = 'ACCOUNT' AND a.entity_id = ua.account_id
)
SELECT
  user_id,
  period_kind,
  period_start,
  CAST(SUM(portfolios_added) AS INT) AS portfolios_added,
  CAST(SUM(clients_added) AS INT) AS clients_added,
  CAST(SUM(accounts_added) AS INT) AS accounts_added
FROM combined
GROUP BY
  user_id,
  period_kind,
  period_start