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

CREATE MATERIALIZED VIEW adib_rm.client_top_allocations_mv AS
SELECT
  cag.client_id,
  cag.type AS account_group_type,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'assetClass',
      pac.asset_class,
      'taxonomyNodeId',
      pac.taxonomy_node_id,
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pac.market_value AS VARCHAR), 'currencyCode', pac.currency_code),
      'fairValue',
      JSONB_BUILD_OBJECT('amount', CAST(pac.fair_value AS VARCHAR), 'currencyCode', pac.currency_code),
      'weight',
      pac.weight,
      'rank',
      pac.rank
    ) ORDER BY pac.rank
  ) FILTER(WHERE
    pac.rank <= 5) AS top_allocations
FROM insights.client_to_account_groups_mv AS cag
JOIN (
  SELECT
    account_group_id,
    asset_class,
    taxonomy_node_id,
    market_value,
    fair_value,
    currency_code,
    weight,
    ROW_NUMBER() OVER (PARTITION BY account_group_id ORDER BY market_value DESC, asset_class ASC) AS rank
  FROM (
    SELECT
      i.account_group_id,
      i.item_id AS asset_class,
      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 = 'asset_classes' AND i.position_type = 'POSITION'
  ) AS latest
) AS pac
  ON pac.account_group_id = cag.account_group_id
GROUP BY
  cag.client_id,
  cag.type