CREATE MATERIALIZED VIEW alinma_bff.marketplace_exposure_by_asset_mv AS
SELECT
account_group_id,
asset_id,
market_value_system_currency AS exposure,
CAST('SAR' AS VARCHAR) AS exposure_currency_code,
quantity,
weight,
unrealized_pnl,
total_cost
FROM (
SELECT
account_group_id,
asset_id,
market_value_system_currency,
quantity,
weight,
market_value_system_currency - total_average_cost_system_currency AS unrealized_pnl,
total_average_cost_system_currency AS total_cost,
ROW_NUMBER() OVER (PARTITION BY account_group_id, asset_id ORDER BY dim_balance_date DESC) AS rn
FROM insights.position_by_asset_mv AS position_by_asset_mv_next
WHERE
source_entity_type = 'client'
AND position_type = 'POSITION'
AND NOT market_value_system_currency IS NULL
) AS ranked
WHERE
rn = 1