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

CREATE MATERIALIZED VIEW adib_rm.party_portfolios_mv
WITH (
  backfill_order=FIXED(olap.service_types_dm -> olap.party_involvements_dm)
) AS
WITH party_portfolio_pairs AS (
  SELECT
    inv.party_id,
    inv.entity_id AS portfolio_id,
    MIN(inv.customer_relationship_id) AS customer_relationship_id
  FROM olap.party_involvements_dm AS inv
  JOIN party.customer_relationships AS cr
    ON cr.id = inv.customer_relationship_id
    AND cr.type = 'CUSTOMER'
    AND cr.status = 'ACTIVE'
    AND cr.disabled_at IS NULL
  WHERE
    inv.entity_type = 'PORTFOLIO'
    AND inv.status = 'ACTIVE'
    AND inv.disabled_at IS NULL
    AND inv.effective_from <= CURRENT_TIMESTAMP
    AND (
      inv.effective_to IS NULL OR inv.effective_to > CURRENT_TIMESTAMP
    )
  GROUP BY
    inv.party_id,
    inv.entity_id
)
SELECT
  pp.party_id,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'id',
      p.portfolio_id,
      'name',
      p.name,
      'number',
      p.number,
      'serviceType',
      st.type,
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(ps.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'marketValueSystemCurrency',
      JSONB_BUILD_OBJECT(
        'amount',
        CAST(ps.market_value_system_currency AS VARCHAR),
        'currencyCode',
        lp.base_currency_code
      ),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY p.portfolio_id
  ) AS portfolios
FROM party_portfolio_pairs AS pp
JOIN adib_rm.portfolios_plain_mv AS p
  ON p.portfolio_id = pp.portfolio_id AND p.closing_date IS NULL
JOIN olap.service_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS st
  ON st.service_type_id = p.service_type_id
LEFT JOIN party.lifecycle_profiles AS lp
  ON lp.customer_relationship_id = pp.customer_relationship_id
  AND lp.disabled_at IS NULL
LEFT JOIN insights.portfolio_to_account_groups_mv AS pag
  ON pag.portfolio_id = p.portfolio_id AND pag.type = 'all'
LEFT JOIN insights.position_snapshot_mv AS ps
  ON ps.account_group_id = pag.account_group_id
  AND ps.position_type = 'POSITION'
  AND ps.currency_code = p.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
  ON pnl.account_group_id = ps.account_group_id
  AND pnl.position_type = ps.position_type
  AND pnl.currency_code = ps.currency_code
GROUP BY
  pp.party_id