CREATE MATERIALIZED VIEW insights.asset_distributions_for_consumers_mv AS
SELECT
ad.asset_id,
CASE ad.dimension
WHEN 'ASSET_CLASS'
THEN 'asset_classes'
WHEN 'SECTOR'
THEN 'sectors'
WHEN 'INDUSTRY_GROUP'
THEN 'industry_groups'
WHEN 'INDUSTRY'
THEN 'industries'
WHEN 'SUB_INDUSTRY'
THEN 'sub_industries'
WHEN 'COUNTRY_OF_RISK'
THEN 'country_of_risks'
WHEN 'GEOGRAPHY'
THEN 'regions'
WHEN 'MARKET_CAP'
THEN 'market_caps'
WHEN 'INVESTMENT_STYLE'
THEN 'investment_styles'
WHEN 'LIQUIDITY'
THEN 'liquidities'
WHEN 'FIXED_INCOME_SECTOR'
THEN 'fixed_income_sectors'
WHEN 'BOND_CATEGORY'
THEN 'bond_categories'
WHEN 'CREDIT_QUALITY'
THEN 'credit_qualities'
WHEN 'CURRENCY'
THEN 'currencies'
WHEN 'DEBTOR_DOMICILE'
THEN 'debtor_domiciles'
WHEN 'MATURITY'
THEN 'maturities'
END AS distribution_type,
ad.effective_start_date,
COALESCE(ad.effective_end_date, CAST('9999-12-31' AS DATE)) AS effective_end_date,
tn.code AS item_id,
ad.taxonomy_node_id,
td.code AS taxonomy_code,
ad.percentage AS share
FROM asset_service.asset_distributions_dm AS ad
JOIN asset_service.taxonomy_nodes_dm AS tn
ON tn.node_id = ad.taxonomy_node_id
LEFT JOIN asset_service.taxonomy_dimensions_dm AS td
ON td.dimension_id = tn.dimension_id
WHERE
tn.disabled_at IS NULL