CREATE MATERIALIZED VIEW insights.position_values_mv
WITH (
backfill_order=FIXED(olap.assets_dm -> insights.holding_values_mv, asset_service.foreign_exchange_rates_eod_ft -> insights.holding_values_mv)
) AS
SELECT
h.account_id,
h.asset_id,
atag.account_group_id,
h.dim_value_date,
h.type AS position_type,
h.currency_code AS holding_currency,
atag.base_currency AS group_currency,
a.issue_currency_code AS asset_currency,
COALESCE(a.type, 'UNSPECIFIED') AS asset_type,
h.market_value AS market_value_holding,
h.average_cost AS average_cost_holding,
h.purchased_quantity,
h.fair_value AS fair_value_holding,
h.accrued_value,
COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fx_rate_to_group,
h.market_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS market_value_group,
h.average_cost * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS average_cost_group,
h.fair_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_value_group,
h.accrued_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS accrued_value_group,
ABS(h.market_value) * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS relationship_value_group,
ABS(h.fair_value) * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_relationship_value_group,
h.market_value_system_currency AS market_value_system,
h.average_cost_system_currency AS average_cost_system,
h.fair_value_system_currency AS fair_value_system,
h.accrued_value_system_currency AS accrued_value_system,
ABS(h.market_value_system_currency) AS relationship_value_system,
ABS(h.fair_value_system_currency) AS fair_relationship_value_system
FROM insights.holding_values_mv AS h
JOIN insights.account_to_account_groups_mv AS atag
ON atag.account_id = h.account_id
AND h.dim_value_date >= atag.effective_start_date
AND (
atag.effective_end_date IS NULL OR h.dim_value_date < atag.effective_end_date
)
AND (
atag.opening_date IS NULL OR h.dim_value_date >= atag.opening_date
)
AND atag.source_entity_type <> 'user'
LEFT JOIN olap.assets_dm AS a
ON a.asset_id = h.asset_id
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_group
ON fx_group.source_currency_code = h.currency_code
AND fx_group.target_currency_code = atag.base_currency
AND fx_group.date = h.dim_value_date
WHERE
NOT fx_group.rate IS NULL OR h.currency_code = atag.base_currency