CREATE MATERIALIZED VIEW insights.position_by_asset_mv AS
WITH agg AS (
SELECT
hv.account_group_id,
hv.source_entity_type,
hv.dim_value_date AS dim_balance_date,
COALESCE(hv.position_type, 'POSITION') AS position_type,
hv.asset_id,
hv.asset_currency,
hv.holding_currency,
hv.group_currency AS currency_code,
SUM(hv.market_value_group) AS market_value,
SUM(hv.average_cost_group) AS total_average_cost,
SUM(hv.fair_value_group) AS fair_value,
SUM(hv.accrued_value_group) AS accrued_value,
SUM(hv.market_value_system) AS market_value_system_currency,
SUM(hv.average_cost_system) AS total_average_cost_system_currency,
SUM(hv.fair_value_system) AS fair_value_system_currency,
SUM(hv.accrued_value_system) AS accrued_value_system_currency,
SUM(hv.market_value_holding) AS market_value_holding,
SUM(hv.average_cost_holding) AS total_average_cost_holding,
SUM(hv.fair_value_holding) AS fair_value_holding,
SUM(hv.purchased_quantity) AS quantity
FROM insights.position_values_mv AS hv
GROUP BY
GROUPING SETS (
(
hv.account_group_id,
hv.source_entity_type,
hv.dim_value_date,
hv.position_type,
hv.asset_id,
hv.asset_currency,
hv.holding_currency,
hv.group_currency
),
(
hv.account_group_id,
hv.source_entity_type,
hv.dim_value_date,
hv.asset_id,
hv.asset_currency,
hv.holding_currency,
hv.group_currency
)
)
)
SELECT
p.account_group_id,
p.source_entity_type,
p.dim_balance_date,
p.position_type,
p.asset_id,
p.asset_currency,
p.currency_code,
p.market_value,
p.total_average_cost,
p.fair_value,
p.accrued_value,
p.market_value_system_currency,
p.total_average_cost_system_currency,
p.fair_value_system_currency,
p.accrued_value_system_currency,
p.market_value_holding * COALESCE(
fx_asset.rate,
CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
) AS market_value_asset_currency,
p.total_average_cost_holding * COALESCE(
fx_asset.rate,
CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
) AS total_average_cost_asset_currency,
p.fair_value_holding * COALESCE(
fx_asset.rate,
CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
) AS fair_value_asset_currency,
p.quantity,
p.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
p.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight
FROM agg AS p
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_asset
ON fx_asset.source_currency_code = p.holding_currency
AND fx_asset.target_currency_code = p.asset_currency
AND fx_asset.date = p.dim_balance_date
JOIN insights.position_summary_mv AS b
ON b.account_group_id = p.account_group_id
AND b.dim_balance_date = p.dim_balance_date
AND b.position_type = p.position_type
AND b.currency_code = p.currency_code
WHERE
NOT fx_asset.rate IS NULL OR p.holding_currency = p.asset_currency