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

CREATE MATERIALIZED VIEW opportunity.portfolio_extreme_holding_breaches_mv AS
WITH asset_metrics AS (
  SELECT
    a.account_group_id,
    a.dim_balance_date,
    a.asset_id,
    a.currency_code,
    SUM(a.market_value) AS asset_market_value,
    SUM(a.weight) AS asset_weight,
    'GET_PORTFOLIO_EXTREME_SINGLE_HOLDING_PERCENTAGE' AS activity_name
  FROM (
    SELECT
      account_group_id,
      dim_balance_date,
      asset_id,
      currency_code,
      market_value,
      weight
    FROM insights.position_by_asset_mv AS position_by_asset_mv_next
    WHERE
      source_entity_type = 'portfolio'
      AND position_type = 'POSITION'
      AND NOT asset_id IS NULL
      AND CAST(dim_balance_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS'
  ) AS a
  GROUP BY
    a.account_group_id,
    a.dim_balance_date,
    a.asset_id,
    a.currency_code
)
SELECT
  c.opportunity_id,
  pag.portfolio_id AS resource_id,
  m.dim_balance_date AS fact_date,
  m.asset_id,
  asset.type AS asset_type,
  asset.name_en AS asset_name,
  m.asset_market_value,
  m.currency_code AS asset_currency_code,
  m.asset_weight * 100 AS asset_percentage_value,
  m.asset_weight,
  portfolio.base_currency_code AS portfolio_currency_code,
  m.asset_market_value / m.asset_weight AS portfolio_market_value
FROM asset_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 olap.portfolios_dm AS portfolio
  ON portfolio.portfolio_id = pag.portfolio_id
JOIN asset_service.assets_dm AS asset
  ON asset.id = m.asset_id
JOIN opportunity.opportunity_conditions_mv AS c
  ON c.activity_name = m.activity_name
WHERE
  m.asset_market_value > 0
  AND m.asset_weight > 0
  AND m.asset_weight < 1
  AND CASE c.op
    WHEN 'GTE'
    THEN m.asset_weight >= c.threshold
    WHEN 'GT'
    THEN m.asset_weight > c.threshold
    WHEN 'LTE'
    THEN m.asset_weight <= c.threshold
    WHEN 'LT'
    THEN m.asset_weight < c.threshold
    WHEN 'EQ'
    THEN m.asset_weight = c.threshold
    ELSE FALSE
  END