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

CREATE MATERIALIZED VIEW opportunity.signal_portfolio_balance_decrease_mv AS
WITH signal_metrics AS (
  SELECT
    portfolio_id,
    dim_balance_date,
    'GET_PORTFOLIO_BALANCE_PERCENT_DECREASE_1D' AS activity_name,
    CASE
      WHEN market_value <> 0
      THEN (
        prev_market_value - market_value
      ) / market_value
      ELSE 1
    END AS metric
  FROM opportunity.portfolio_balance_delta_mv AS portfolio_balance_delta_mv_next
  WHERE
    market_value < prev_market_value
  UNION ALL
  SELECT
    portfolio_id,
    dim_balance_date,
    'GET_PORTFOLIO_BALANCE_ABSOLUTE_DECREASE_1D' AS activity_name,
    prev_market_value - market_value AS metric
  FROM opportunity.portfolio_balance_delta_mv AS portfolio_balance_delta_mv_next
  WHERE
    market_value < prev_market_value
)
SELECT
  c.opportunity_id,
  c.activity_name,
  s.portfolio_id AS resource_id,
  s.dim_balance_date AS fact_date
FROM signal_metrics AS s
JOIN opportunity.opportunity_conditions_mv AS c
  ON c.activity_name = s.activity_name
WHERE
  CASE c.op
    WHEN 'GTE'
    THEN s.metric >= c.threshold
    WHEN 'GT'
    THEN s.metric > c.threshold
    WHEN 'LTE'
    THEN s.metric <= c.threshold
    WHEN 'LT'
    THEN s.metric < c.threshold
    WHEN 'EQ'
    THEN s.metric = c.threshold
    ELSE FALSE
  END