CREATE MATERIALIZED VIEW insights.position_by_distribution_mv AS
WITH by_distribution AS (
SELECT
av.account_group_id,
av.source_entity_type,
av.dim_value_date AS dim_balance_date,
av.position_type,
ad.distribution_type,
ad.taxonomy_node_id,
ad.taxonomy_code,
av.group_currency AS currency_code,
SUM(av.market_value * ad.share) AS market_value,
SUM(av.average_cost * ad.share) AS total_average_cost,
SUM(av.fair_value * ad.share) AS fair_value,
SUM(av.accrued_value * ad.share) AS accrued_value,
SUM(av.market_value_system * ad.share) AS market_value_system_currency,
SUM(av.average_cost_system * ad.share) AS total_average_cost_system_currency,
SUM(av.fair_value_system * ad.share) AS fair_value_system_currency,
SUM(av.accrued_value_system * ad.share) AS accrued_value_system_currency
FROM insights.position_asset_values_mv AS av
JOIN insights.asset_distributions_week_binned_mv AS ad
ON ad.asset_id = av.asset_id
AND ad.dim_value_week = CAST(DATE_TRUNC('WEEK', av.dim_value_date) AS DATE)
AND av.dim_value_date >= ad.effective_start_date
AND av.dim_value_date < ad.effective_end_date
GROUP BY
av.account_group_id,
av.source_entity_type,
av.dim_value_date,
av.group_currency,
av.position_type,
ad.distribution_type,
ad.taxonomy_node_id,
ad.taxonomy_code
)
SELECT
d.account_group_id,
d.source_entity_type,
d.dim_balance_date,
d.position_type,
d.distribution_type,
d.taxonomy_node_id,
d.taxonomy_code,
d.currency_code,
d.market_value,
d.total_average_cost,
d.fair_value,
d.accrued_value,
d.market_value_system_currency,
d.total_average_cost_system_currency,
d.fair_value_system_currency,
d.accrued_value_system_currency,
d.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
d.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight
FROM by_distribution AS d
JOIN insights.position_summary_mv AS b
ON b.account_group_id = d.account_group_id
AND b.dim_balance_date = d.dim_balance_date
AND b.position_type = d.position_type
AND b.currency_code = d.currency_code