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

CREATE MATERIALIZED VIEW opportunity.portfolio_allocation_drift_breaches_mv AS
WITH drift_metrics AS (
  SELECT
    pag.portfolio_id AS resource_id,
    p.dim_balance_date,
    p.taxonomy_node_id,
    p.weight AS current_allocation,
    b.weight AS benchmark_allocation,
    (
      p.weight - b.weight
    ) AS drift_percentage,
    'GET_PORTFOLIO_ALLOCATION_DRIFT_PERCENTAGE' AS activity_name
  FROM insights.position_by_distribution_mv AS p
  JOIN insights.portfolio_to_account_groups_mv AS pag
    ON pag.account_group_id = p.account_group_id AND pag.type = 'all'
  JOIN olap.portfolios_dm AS pd
    ON pd.portfolio_id = pag.portfolio_id
  JOIN insights.benchmark_values_by_distribution_mv AS b
    ON b.benchmark_id = pd.benchmark_id
    AND b.fact_date = p.dim_balance_date
    AND b.distribution_type = p.distribution_type
    AND b.taxonomy_node_id = p.taxonomy_node_id
  WHERE
    p.distribution_type = 'asset_classes'
    AND p.source_entity_type = 'portfolio'
    AND p.position_type = 'POSITION'
    AND CAST(p.dim_balance_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS'
)
SELECT
  c.opportunity_id,
  m.resource_id,
  m.dim_balance_date AS fact_date,
  m.taxonomy_node_id,
  m.current_allocation,
  m.benchmark_allocation,
  m.drift_percentage
FROM drift_metrics AS m
JOIN opportunity.opportunity_conditions_mv AS c
  ON c.activity_name = m.activity_name
WHERE
  CASE c.op
    WHEN 'GTE'
    THEN m.drift_percentage >= c.threshold
    WHEN 'GT'
    THEN m.drift_percentage > c.threshold
    WHEN 'LTE'
    THEN m.drift_percentage <= c.threshold
    WHEN 'LT'
    THEN m.drift_percentage < c.threshold
    WHEN 'EQ'
    THEN m.drift_percentage = c.threshold
    ELSE FALSE
  END