RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW alpheya_experience_bff.party_task_unique_mv AS
SELECT
  party_id,
  task_id,
  status,
  priority
FROM (
  SELECT
    party_id,
    task_id,
    status,
    priority,
    ROW_NUMBER() OVER (PARTITION BY party_id, task_id ORDER BY task_id) AS rn
  FROM (
    SELECT
      cpa.party_id,
      t.task_id,
      t.status,
      t.priority
    FROM crm_service.tasks_dm AS t
    JOIN (
      SELECT
        party_id,
        account_id
      FROM insights.party_account_direct_mv AS party_account_direct_mv_next
      WHERE
        effective_start_date <= CURRENT_TIMESTAMP
        AND (
          effective_end_date IS NULL OR effective_end_date > CURRENT_TIMESTAMP
        )
    ) AS cpa
      ON cpa.account_id = t.resource_account_id
    JOIN alpheya_experience_bff.party_active_customer_parties_mv AS acp
      ON acp.party_id = cpa.party_id
    WHERE
      t.disabled_at IS NULL AND t.status IN ('TO_DO', 'IN_PROGRESS')
    UNION ALL
    SELECT
      pp.party_id,
      t.task_id,
      t.status,
      t.priority
    FROM crm_service.tasks_dm AS t
    JOIN alpheya_experience_bff.party_active_portfolio_involvements_mv AS pp
      ON pp.portfolio_id = t.resource_portfolio_id
    WHERE
      t.disabled_at IS NULL AND t.status IN ('TO_DO', 'IN_PROGRESS')
  )
)
WHERE
  rn <= 1