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
  ) 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,
    rank
  FROM (
    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.taxonomy_node_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 ranked
  WHERE
    ranked.rank <= 5
) AS ps
  ON ps.account_group_id = cag.account_group_id
GROUP BY
  cag.client_id,
  cag.type