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