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

CREATE MATERIALIZED VIEW insights.party_account_via_portfolio_mv AS
WITH portfolio_mediated AS (
  SELECT
    ppi.party_id,
    ppi.customer_relationship_id,
    atp.account_id,
    GREATEST(atp.effective_start_date, ppi.effective_from) AS effective_start_date,
    CASE
      WHEN atp.effective_end_date IS NULL AND ppi.effective_to IS NULL
      THEN CAST(NULL AS DATE)
      WHEN atp.effective_end_date IS NULL
      THEN ppi.effective_to
      WHEN ppi.effective_to IS NULL
      THEN atp.effective_end_date
      ELSE LEAST(atp.effective_end_date, ppi.effective_to)
    END AS effective_end_date,
    oa.is_restricted
  FROM olap.account_to_portfolios_dm AS atp
  JOIN olap.party_involvements_dm AS ppi
    ON ppi.entity_id = atp.portfolio_id
    AND ppi.entity_type = 'PORTFOLIO'
    AND ppi.involvement_type IN ('PORTFOLIO_HOLDER', 'JOINT_PORTFOLIO_HOLDER')
    AND ppi.status = 'ACTIVE'
    AND ppi.disabled_at IS NULL
    AND (
      ppi.effective_to IS NULL OR ppi.effective_to > ppi.effective_from
    )
  JOIN insights.open_accounts_mv AS oa
    ON oa.account_id = atp.account_id
  JOIN party.customer_relationships AS cr
    ON cr.id = ppi.customer_relationship_id
    AND cr.type = 'CUSTOMER'
    AND cr.status = 'ACTIVE'
    AND cr.disabled_at IS NULL
  WHERE
    atp.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.party_involvements_dm AS d
      JOIN party.customer_relationships AS dcr
        ON dcr.id = d.customer_relationship_id
        AND dcr.type = 'CUSTOMER'
        AND dcr.status = 'ACTIVE'
        AND dcr.disabled_at IS NULL
      WHERE
        d.entity_id = atp.account_id
        AND d.party_id = ppi.party_id
        AND d.entity_type = 'ACCOUNT'
        AND d.involvement_type IN ('ACCOUNT_HOLDER', 'JOINT_ACCOUNT_HOLDER')
        AND d.status = 'ACTIVE'
        AND d.disabled_at IS NULL
        AND (
          d.effective_to IS NULL OR d.effective_to > d.effective_from
        )
    )
)
SELECT
  party_id,
  customer_relationship_id,
  account_id,
  effective_start_date,
  effective_end_date,
  CAST('all' AS VARCHAR) AS type
FROM portfolio_mediated
WHERE
  effective_end_date IS NULL OR effective_start_date < effective_end_date
UNION ALL
SELECT
  party_id,
  customer_relationship_id,
  account_id,
  effective_start_date,
  effective_end_date,
  CAST(CASE WHEN is_restricted THEN 'restricted' ELSE 'un_restricted' END AS VARCHAR) AS type
FROM portfolio_mediated
WHERE
  effective_end_date IS NULL OR effective_start_date < effective_end_date