CREATE MATERIALIZED VIEW insights.intraday_position_by_asset_mv AS
SELECT
av.account_group_id,
av.position_type,
av.asset_id,
a.issue_currency_code AS asset_currency,
av.group_currency AS currency_code,
av.market_value,
av.average_cost AS total_average_cost,
av.fair_value,
av.accrued_value,
av.market_value_system AS market_value_system_currency,
av.average_cost_system AS total_average_cost_system_currency,
av.fair_value_system AS fair_value_system_currency,
av.accrued_value_system AS accrued_value_system_currency,
av.market_value_asset AS market_value_asset_currency,
av.average_cost_asset AS total_average_cost_asset_currency,
av.fair_value_asset AS fair_value_asset_currency,
av.quantity,
av.market_value_system / NULLIF(b.market_value_system_currency, 0) AS weight,
av.fair_value_system / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight,
av.change_time
FROM insights.intraday_position_asset_values_mv AS av
LEFT JOIN asset_service.assets_dm AS a
ON a.id = av.asset_id
JOIN insights.intraday_position_summary_mv AS b
ON b.account_group_id = av.account_group_id AND b.position_type = av.position_type