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

CREATE MATERIALIZED VIEW adib_rm.sdk_home_overview_mv AS
SELECT
  c.id AS client_id,
  c.base_currency_code,
  lab.market_value AS total_market_value,
  lab.market_value_system_currency AS total_market_value_system_currency,
  JSONB_BUILD_OBJECT(
    'value',
    JSONB_BUILD_OBJECT(
      'amount',
      CAST(pnl.unrealized_gain_loss AS VARCHAR),
      'currencyCode',
      c.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
  ) AS unrealized_gain_loss,
  COALESCE(pf.portfolios, CAST('[]' AS JSONB)) AS portfolios,
  COALESCE(ct.cash_tiles, CAST('[]' AS JSONB)) AS cash_tiles,
  CAST('[]' AS JSONB) AS favourites,
  COALESCE(ta.top_allocations, CAST('[]' AS JSONB)) AS top_allocations,
  COALESCE(taty.top_allocations_asset_type, CAST('[]' AS JSONB)) AS top_allocations_asset_type,
  COALESCE(ts.top_allocations_sector, CAST('[]' AS JSONB)) AS top_allocations_sector
FROM olap.clients_dm AS c
LEFT JOIN insights.client_to_account_groups_mv AS cag
  ON c.id = cag.client_id AND cag.type = 'all'
LEFT JOIN insights.position_snapshot_mv AS lab
  ON cag.account_group_id = lab.account_group_id
  AND lab.position_type = 'POSITION'
  AND lab.currency_code = c.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
  ON cag.account_group_id = pnl.account_group_id
  AND pnl.position_type = 'POSITION'
  AND pnl.currency_code = c.base_currency_code
LEFT JOIN adib_rm.client_portfolios_mv AS pf
  ON pf.client_id = c.id
LEFT JOIN adib_rm.client_cash_tiles_mv AS ct
  ON ct.client_id = c.id
LEFT JOIN adib_rm.client_top_allocations_mv AS ta
  ON ta.client_id = c.id AND ta.account_group_type = 'all'
LEFT JOIN adib_rm.client_top_allocations_by_asset_type_mv AS taty
  ON taty.client_id = c.id AND taty.account_group_type = 'all'
LEFT JOIN adib_rm.client_top_allocations_by_sector_mv AS ts
  ON ts.client_id = c.id AND ts.account_group_type = 'all'
WHERE
  c.disabled_at IS NULL