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

CREATE MATERIALIZED VIEW opportunity.signal_portfolio_balance_increase_mv AS
WITH signal_metrics AS (
  SELECT
    portfolio_id,
    dim_balance_date,
    'GET_PORTFOLIO_BALANCE_PERCENT_INCREASE_1D' AS activity_name,
    (
      market_value - prev_market_value
    ) / prev_market_value 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_INCREASE_1D' AS activity_name,
    market_value - prev_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