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

CREATE MATERIALIZED VIEW opportunity.portfolio_days_since_transaction_breaches_mv AS
WITH conditions AS (
  SELECT
    opportunity_id,
    activity_name,
    op,
    threshold,
    as_of_date
  FROM opportunity.opportunity_conditions_mv AS opportunity_conditions_mv_next
  WHERE
    activity_name = 'GET_PORTFOLIO_DAYS_SINCE_LAST_TRANSACTION'
    AND NOT as_of_date IS NULL
), matched AS (
  SELECT
    c.opportunity_id,
    p.portfolio_id,
    c.as_of_date,
    c.activity_name,
    p.last_transaction_date,
    c.op,
    c.threshold,
    (
      c.as_of_date - p.last_transaction_date
    ) AS days_since
  FROM opportunity.portfolio_last_transaction_mv AS p
  JOIN conditions AS c
    ON c.activity_name = p.activity_name
)
SELECT
  opportunity_id,
  portfolio_id AS resource_id,
  as_of_date AS fact_date,
  activity_name,
  last_transaction_date,
  days_since
FROM matched
WHERE
  CASE op
    WHEN 'GTE'
    THEN days_since >= threshold
    WHEN 'GT'
    THEN days_since > threshold
    WHEN 'LTE'
    THEN days_since <= threshold
    WHEN 'LT'
    THEN days_since < threshold
    WHEN 'EQ'
    THEN days_since = threshold
    ELSE FALSE
  END