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

CREATE MATERIALIZED VIEW adib_rm.client_top_allocations_by_sector_mv AS
SELECT
  cag.client_id,
  cag.type AS account_group_type,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'sector',
      ps.sector,
      'taxonomyNodeId',
      ps.taxonomy_node_id,
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(ps.market_value AS VARCHAR), 'currencyCode', ps.currency_code),
      'fairValue',
      JSONB_BUILD_OBJECT('amount', CAST(ps.fair_value AS VARCHAR), 'currencyCode', ps.currency_code),
      'weight',
      ps.weight,
      'rank',
      ps.rank
    ) ORDER BY ps.rank
  ) FILTER(WHERE
    ps.rank <= 5) AS top_allocations_sector
FROM insights.client_to_account_groups_mv AS cag
JOIN (
  SELECT
    account_group_id,
    sector,
    taxonomy_node_id,
    market_value,
    fair_value,
    currency_code,
    weight,
    ROW_NUMBER() OVER (PARTITION BY account_group_id ORDER BY market_value DESC, sector ASC) AS rank
  FROM (
    SELECT
      i.account_group_id,
      i.item_id AS sector,
      i.taxonomy_node_id,
      i.market_value,
      i.fair_value,
      i.currency_code,
      i.weight
    FROM insights.intraday_position_by_distribution_mv AS i
    WHERE
      i.distribution_type = 'sectors' AND i.position_type = 'POSITION'
  ) AS latest
) AS ps
  ON ps.account_group_id = cag.account_group_id
GROUP BY
  cag.client_id,
  cag.type