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,
  COALESCE(ips.market_value, lab.market_value) AS total_market_value,
  CASE
    WHEN NOT ips.market_value IS NULL
    THEN ips.market_value_system_currency
    ELSE lab.market_value_system_currency
  END AS total_market_value_system_currency,
  JSONB_BUILD_OBJECT(
    'value',
    JSONB_BUILD_OBJECT(
      'amount',
      CASE
        WHEN NOT ips.market_value IS NULL
        THEN CAST((
          ips.market_value - ips.total_average_cost
        ) AS VARCHAR)
        ELSE CAST(pnl.unrealized_gain_loss AS VARCHAR)
      END,
      'currencyCode',
      c.base_currency_code
    ),
    'percentage',
    CASE
      WHEN NOT ips.market_value IS NULL
      THEN CASE
        WHEN ips.total_average_cost IS NULL OR ips.total_average_cost = 0
        THEN NULL
        ELSE CAST((
          (
            ips.market_value - ips.total_average_cost
          ) / ips.total_average_cost
        ) AS VARCHAR)
      END
      ELSE 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
        ) AS VARCHAR)
      END
    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 insights.intraday_position_summary_mv AS ips
  ON cag.account_group_id = ips.account_group_id
  AND ips.position_type = 'POSITION'
  AND ips.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