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

CREATE MATERIALIZED VIEW adib_rm.sdk_home_overview_party_mv AS
WITH party_lifecycle AS (
  SELECT
    cr.party_id,
    MIN(lp.base_currency_code) AS base_currency_code
  FROM party.customer_relationships AS cr
  JOIN party.lifecycle_profiles AS lp
    ON lp.customer_relationship_id = cr.id AND lp.disabled_at IS NULL
  WHERE
    cr.type = 'CUSTOMER' AND cr.status = 'ACTIVE' AND cr.disabled_at IS NULL
  GROUP BY
    cr.party_id
)
SELECT
  p.id AS party_id,
  pl.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',
      pl.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 party.parties AS p
JOIN party_lifecycle AS pl
  ON pl.party_id = p.id
LEFT JOIN insights.party_to_account_groups_mv AS pag
  ON pag.party_id = p.id AND pag.type = 'all'
LEFT JOIN insights.position_snapshot_mv AS lab
  ON pag.account_group_id = lab.account_group_id
  AND lab.position_type = 'POSITION'
  AND lab.currency_code = pl.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
  ON pag.account_group_id = pnl.account_group_id
  AND pnl.position_type = 'POSITION'
  AND pnl.currency_code = pl.base_currency_code
LEFT JOIN insights.intraday_position_summary_mv AS ips
  ON pag.account_group_id = ips.account_group_id
  AND ips.position_type = 'POSITION'
  AND ips.currency_code = pl.base_currency_code
LEFT JOIN adib_rm.party_portfolios_mv AS pf
  ON pf.party_id = p.id
LEFT JOIN adib_rm.party_cash_tiles_mv AS ct
  ON ct.party_id = p.id
LEFT JOIN adib_rm.party_top_allocations_mv AS ta
  ON ta.party_id = p.id AND ta.account_group_type = 'all'
LEFT JOIN adib_rm.party_top_allocations_by_asset_type_mv AS taty
  ON taty.party_id = p.id AND taty.account_group_type = 'all'
LEFT JOIN adib_rm.party_top_allocations_by_sector_mv AS ts
  ON ts.party_id = p.id AND ts.account_group_type = 'all'
WHERE
  p.disabled_at IS NULL