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

CREATE MATERIALIZED VIEW opportunity.portfolio_extreme_sector_breaches_mv AS
WITH sector_metrics AS (
  SELECT
    account_group_id,
    dim_balance_date,
    taxonomy_node_id,
    weight AS sector_weight,
    'GET_PORTFOLIO_EXTREME_SINGLE_SECTOR_PERCENTAGE' AS activity_name
  FROM insights.position_by_distribution_mv AS position_by_distribution_mv_next
  WHERE
    distribution_type = 'sectors'
    AND source_entity_type = 'portfolio'
    AND position_type = 'ASSET'
    AND weight > 0
    AND weight <= 1
    AND CAST(dim_balance_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS'
)
SELECT
  c.opportunity_id,
  pag.portfolio_id AS resource_id,
  m.dim_balance_date AS fact_date,
  m.taxonomy_node_id,
  m.sector_weight * 100 AS sector_percentage_value,
  m.sector_weight
FROM sector_metrics AS m
JOIN insights.portfolio_to_account_groups_mv AS pag
  ON pag.account_group_id = m.account_group_id AND pag.type = 'all'
JOIN opportunity.opportunity_conditions_mv AS c
  ON c.activity_name = m.activity_name
WHERE
  CASE c.op
    WHEN 'GTE'
    THEN m.sector_weight >= c.threshold
    WHEN 'GT'
    THEN m.sector_weight > c.threshold
    WHEN 'LTE'
    THEN m.sector_weight <= c.threshold
    WHEN 'LT'
    THEN m.sector_weight < c.threshold
    WHEN 'EQ'
    THEN m.sector_weight = c.threshold
    ELSE FALSE
  END