RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW alpheya_experience_bff.report_holdings_daily_mv AS
SELECT
  pag.portfolio_id,
  pag.type AS account_group_type,
  a.dim_balance_date,
  a.asset_id,
  ast.name_en AS asset_name_en,
  ast.name_ar AS asset_name_ar,
  ast.type AS asset_type,
  ast.ticker,
  ast.isin,
  a.asset_currency,
  a.currency_code,
  a.market_value,
  a.total_average_cost,
  a.fair_value,
  a.accrued_value,
  a.market_value_system_currency,
  a.fair_value_system_currency,
  a.market_value_asset_currency,
  a.fair_value_asset_currency,
  a.total_average_cost_asset_currency,
  a.quantity,
  a.weight,
  a.fair_value_weight,
  pxl.adjusted_last_close_price AS unit_price
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN insights.position_by_asset_mv AS a
  ON a.account_group_id = pag.account_group_id
  AND a.position_type = 'POSITION'
  AND a.source_entity_type = 'portfolio'
LEFT JOIN asset_service.assets_dm AS ast
  ON ast.id = a.asset_id
LEFT JOIN insights.asset_price_with_lag_mv AS pxl
  ON pxl.asset_id = a.asset_id AND pxl.fact_date = a.dim_balance_date