CREATE MATERIALIZED VIEW insights.position_snapshot_mv AS
WITH converted AS (
SELECT
atag.account_group_id,
atag.base_currency,
h.type AS position_type,
h.dim_value_date,
h.market_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS market_value,
h.fair_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_value,
h.accrued_value * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS accrued_value,
ABS(h.market_value) * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS relationship_value,
ABS(h.fair_value) * COALESCE(fx.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_relationship_value,
h.market_value_system_currency,
h.fair_value_system_currency,
h.accrued_value_system_currency,
ABS(h.market_value_system_currency) AS relationship_value_system_currency,
ABS(h.fair_value_system_currency) AS fair_relationship_value_system_currency
FROM insights.holding_values_latest_mv AS h
JOIN insights.account_to_account_groups_mv AS atag
ON atag.account_id = h.account_id
AND atag.effective_end_date IS NULL
AND atag.source_entity_type <> 'user'
LEFT JOIN insights.fx_rates_snapshot_mv AS fx
ON fx.source_currency_code = h.currency_code
AND fx.target_currency_code = atag.base_currency
WHERE
NOT fx.rate IS NULL OR h.currency_code = atag.base_currency
)
SELECT
account_group_id,
MAX(dim_value_date) AS dim_balance_date,
COALESCE(position_type, 'POSITION') AS position_type,
base_currency AS currency_code,
SUM(market_value) AS market_value,
SUM(fair_value) AS fair_value,
SUM(accrued_value) AS accrued_value,
SUM(market_value_system_currency) AS market_value_system_currency,
SUM(fair_value_system_currency) AS fair_value_system_currency,
SUM(accrued_value_system_currency) AS accrued_value_system_currency,
SUM(relationship_value) AS relationship_value,
SUM(fair_relationship_value) AS fair_relationship_value,
SUM(relationship_value_system_currency) AS relationship_value_system_currency,
SUM(fair_relationship_value_system_currency) AS fair_relationship_value_system_currency
FROM converted
GROUP BY
GROUPING SETS (
(account_group_id, base_currency, position_type),
(account_group_id, base_currency)
)