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

CREATE MATERIALIZED VIEW insights.account_to_account_groups_mv AS
SELECT
  windowed.account_id,
  windowed.account_group_id,
  windowed.effective_start_date,
  CASE
    WHEN windowed.effective_end_date IS NULL AND windowed.next_start IS NULL
    THEN CAST(NULL AS DATE)
    WHEN windowed.effective_end_date IS NULL
    THEN windowed.next_start
    WHEN windowed.next_start IS NULL
    THEN windowed.effective_end_date
    ELSE LEAST(windowed.effective_end_date, windowed.next_start)
  END AS effective_end_date,
  ag.base_currency,
  ag.opening_date,
  ag.source_entity_type
FROM (
  SELECT
    account_id,
    account_group_id,
    effective_start_date,
    effective_end_date,
    LEAD(effective_start_date) OVER (PARTITION BY account_id, account_group_id ORDER BY effective_start_date) AS next_start
  FROM (
    SELECT
      oa.account_id,
      'account_group_' || MD5(CAST((
        oa.account_id || 'all'
      ) AS BYTEA)) AS account_group_id,
      CAST('1970-01-01' AS DATE) AS effective_start_date,
      CAST(NULL AS DATE) AS effective_end_date
    FROM insights.open_accounts_mv AS oa
    UNION ALL
    SELECT
      d.account_id,
      'account_group_' || MD5(CAST((
        d.client_id || d.type
      ) AS BYTEA)) AS account_group_id,
      d.effective_start_date,
      d.effective_end_date
    FROM insights.client_account_direct_mv AS d
    UNION ALL
    SELECT
      p.account_id,
      'account_group_' || MD5(CAST((
        p.client_id || p.type
      ) AS BYTEA)) AS account_group_id,
      p.effective_start_date,
      p.effective_end_date
    FROM insights.client_account_via_portfolio_mv AS p
    UNION ALL
    SELECT
      atp.account_id,
      'account_group_' || MD5(CAST((
        atp.portfolio_id || 'all'
      ) AS BYTEA)) AS account_group_id,
      atp.effective_start_date,
      atp.effective_end_date
    FROM olap.account_to_portfolios_dm AS atp
    JOIN insights.open_accounts_mv AS oa
      ON oa.account_id = atp.account_id
    WHERE
      atp.disabled_at IS NULL
      AND (
        atp.effective_end_date IS NULL
        OR atp.effective_end_date > atp.effective_start_date
      )
    UNION ALL
    SELECT
      atp.account_id,
      'account_group_' || MD5(CAST((
        atp.portfolio_id || 'restricted'
      ) AS BYTEA)) AS account_group_id,
      atp.effective_start_date,
      atp.effective_end_date
    FROM olap.account_to_portfolios_dm AS atp
    JOIN insights.open_accounts_mv AS oa
      ON oa.account_id = atp.account_id
    WHERE
      atp.disabled_at IS NULL
      AND (
        atp.effective_end_date IS NULL
        OR atp.effective_end_date > atp.effective_start_date
      )
      AND oa.is_restricted = TRUE
    UNION ALL
    SELECT
      atp.account_id,
      'account_group_' || MD5(CAST((
        atp.portfolio_id || 'un_restricted'
      ) AS BYTEA)) AS account_group_id,
      atp.effective_start_date,
      atp.effective_end_date
    FROM olap.account_to_portfolios_dm AS atp
    JOIN insights.open_accounts_mv AS oa
      ON oa.account_id = atp.account_id
    WHERE
      atp.disabled_at IS NULL
      AND (
        atp.effective_end_date IS NULL
        OR atp.effective_end_date > atp.effective_start_date
      )
      AND NOT oa.is_restricted IS TRUE
    UNION ALL
    SELECT
      uam.account_id,
      'account_group_' || MD5(CAST((
        uam.user_id || uam.type
      ) AS BYTEA)) AS account_group_id,
      CAST('1970-01-01' AS DATE) AS effective_start_date,
      CAST(NULL AS DATE) AS effective_end_date
    FROM insights.user_account_membership_mv AS uam
    UNION ALL
    SELECT
      pad.account_id,
      'account_group_' || MD5(CAST((
        pad.party_id || pad.type
      ) AS BYTEA)) AS account_group_id,
      pad.effective_start_date,
      pad.effective_end_date
    FROM insights.party_account_direct_mv AS pad
  ) AS raw
) AS windowed
JOIN insights.account_groups_mv AS ag
  ON ag.account_group_id = windowed.account_group_id