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

CREATE MATERIALIZED VIEW insights.client_account_via_portfolio_mv AS
SELECT
  client_id,
  account_id,
  effective_start_date,
  effective_end_date,
  type
FROM (
  SELECT
    cp.client_id,
    atp.account_id,
    GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
    CASE
      WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
      THEN CAST(NULL AS DATE)
      WHEN atp.effective_end_date IS NULL
      THEN cp.effective_end_date
      WHEN cp.effective_end_date IS NULL
      THEN atp.effective_end_date
      ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
    END AS effective_end_date,
    CAST('all' AS VARCHAR) AS type
  FROM olap.account_to_portfolios_dm AS atp
  JOIN olap.clients_portfolios_dm AS cp
    ON cp.portfolio_id = atp.portfolio_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  WHERE
    atp.disabled_at IS NULL
    AND cp.disabled_at IS NULL
    AND (
      atp.effective_end_date IS NULL
      OR atp.effective_end_date > atp.effective_start_date
    )
    AND NOT EXISTS(
      SELECT
        1
      FROM olap.accounts_to_clients_dm AS atc
      WHERE
        atc.account_id = atp.account_id
        AND atc.client_id = cp.client_id
        AND atc.disabled_at IS NULL
        AND (
          atc.effective_end_date IS NULL
          OR atc.effective_end_date > atc.effective_start_date
        )
    )
  UNION ALL
  SELECT
    cp.client_id,
    atp.account_id,
    GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
    CASE
      WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
      THEN CAST(NULL AS DATE)
      WHEN atp.effective_end_date IS NULL
      THEN cp.effective_end_date
      WHEN cp.effective_end_date IS NULL
      THEN atp.effective_end_date
      ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
    END AS effective_end_date,
    CAST('restricted' AS VARCHAR) AS type
  FROM olap.account_to_portfolios_dm AS atp
  JOIN olap.clients_portfolios_dm AS cp
    ON cp.portfolio_id = atp.portfolio_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  WHERE
    atp.disabled_at IS NULL
    AND cp.disabled_at IS NULL
    AND (
      atp.effective_end_date IS NULL
      OR atp.effective_end_date > atp.effective_start_date
    )
    AND NOT EXISTS(
      SELECT
        1
      FROM olap.accounts_to_clients_dm AS atc
      WHERE
        atc.account_id = atp.account_id
        AND atc.client_id = cp.client_id
        AND atc.disabled_at IS NULL
        AND (
          atc.effective_end_date IS NULL
          OR atc.effective_end_date > atc.effective_start_date
        )
    )
    AND oa.is_restricted = TRUE
  UNION ALL
  SELECT
    cp.client_id,
    atp.account_id,
    GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
    CASE
      WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
      THEN CAST(NULL AS DATE)
      WHEN atp.effective_end_date IS NULL
      THEN cp.effective_end_date
      WHEN cp.effective_end_date IS NULL
      THEN atp.effective_end_date
      ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
    END AS effective_end_date,
    CAST('un_restricted' AS VARCHAR) AS type
  FROM olap.account_to_portfolios_dm AS atp
  JOIN olap.clients_portfolios_dm AS cp
    ON cp.portfolio_id = atp.portfolio_id
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  WHERE
    atp.disabled_at IS NULL
    AND cp.disabled_at IS NULL
    AND (
      atp.effective_end_date IS NULL
      OR atp.effective_end_date > atp.effective_start_date
    )
    AND NOT EXISTS(
      SELECT
        1
      FROM olap.accounts_to_clients_dm AS atc
      WHERE
        atc.account_id = atp.account_id
        AND atc.client_id = cp.client_id
        AND atc.disabled_at IS NULL
        AND (
          atc.effective_end_date IS NULL
          OR atc.effective_end_date > atc.effective_start_date
        )
    )
    AND NOT oa.is_restricted IS TRUE
) AS intervals
WHERE
  effective_end_date IS NULL OR effective_start_date < effective_end_date