asset_distributions_sink_input_mv
materialized_view
view diff
- ad.asset_id,
- CASE ad.dimension
- WHEN 'ASSET_CLASS'
- THEN 'asset_classes'
- WHEN 'SECTOR'
- THEN 'sectors'
- WHEN 'GEOGRAPHY'
- THEN 'regions'
- WHEN 'CREDIT_QUALITY'
- THEN 'credit_qualities'
- WHEN 'CURRENCY'
- THEN 'currencies'
- END AS distribution_type,
- ad.effective_start_date,
- ad.effective_end_date,
- JSONB_OBJECT_AGG(tn.code, ad.percentage) AS distribution,
- JSONB_OBJECT_AGG(tn.code, ad.taxonomy_node_id) AS taxonomy_node_ids
-FROM asset_service.asset_distributions_dm AS ad
-JOIN asset_service.taxonomy_nodes_dm AS tn
- ON tn.node_id = ad.taxonomy_node_id
-WHERE
- tn.disabled_at IS NULL
+ asset_id,
+ distribution_type,
+ effective_start_date,
+ NULLIF(effective_end_date, CAST('9999-12-31' AS DATE)) AS effective_end_date,
+ JSONB_OBJECT_AGG(item_id, share) AS distribution,
+ JSONB_OBJECT_AGG(item_id, taxonomy_node_id) AS taxonomy_node_ids
+FROM insights.asset_distributions_for_consumers_mv
- ad.asset_id,
- ad.dimension,
- ad.effective_start_date,
- ad.effective_end_date
+ asset_id,
+ distribution_type,
+ effective_start_date,
+ effective_end_date
benchmark_twrr_by_asset_type_mv
materialized_view
view diff
- backfill_order=FIXED(olap.assets_dm -> olap.basket_index_benchmark_constituents_ft)
+ backfill_order=FIXED(
+ olap.assets_dm -> insights.benchmark_twrr_by_asset_mv
+ )
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
- SUM(a.daily_subperiod_return * c.weight) AS daily_subperiod_return
-FROM olap.basket_index_benchmark_constituents_ft AS c
-JOIN insights.asset_twrr_mv AS a
- ON a.asset_id = c.asset_id AND a.fact_date = c.fact_date
-JOIN olap.assets_dm AS ad
- ON ad.asset_id = c.asset_id
-WHERE
- c.disabled_at IS NULL AND NOT a.daily_subperiod_return IS NULL
+ SUM(b.daily_subperiod_return) AS daily_subperiod_return
+FROM insights.benchmark_twrr_by_asset_mv AS b
+INNER JOIN olap.assets_dm AS ad
+ ON ad.asset_id = b.asset_id
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
benchmark_twrr_by_distribution_mv
materialized_view
view diff
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
- SUM(a.daily_subperiod_return * c.weight * ad.share) AS daily_subperiod_return
-FROM olap.basket_index_benchmark_constituents_ft AS c
-JOIN insights.asset_twrr_mv AS a
- ON a.asset_id = c.asset_id AND a.fact_date = c.fact_date
+ SUM(b.daily_subperiod_return * ad.share) AS daily_subperiod_return
+FROM insights.benchmark_twrr_by_asset_mv AS b
- ON ad.asset_id = c.asset_id
- AND c.fact_date >= ad.effective_start_date
- AND c.fact_date < ad.effective_end_date
-WHERE
- c.disabled_at IS NULL AND NOT a.daily_subperiod_return IS NULL
+ ON ad.asset_id = b.asset_id
+ AND b.fact_date >= ad.effective_start_date
+ AND b.fact_date < ad.effective_end_date
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
benchmark_twrr_by_currency_mv
materialized_view
view diff
- backfill_order=FIXED(olap.assets_dm -> olap.basket_index_benchmark_constituents_ft)
+ backfill_order=FIXED(
+ olap.assets_dm -> insights.benchmark_twrr_by_asset_mv
+ )
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
- SUM(a.daily_subperiod_return * c.weight) AS daily_subperiod_return
-FROM olap.basket_index_benchmark_constituents_ft AS c
-JOIN insights.asset_twrr_mv AS a
- ON a.asset_id = c.asset_id AND a.fact_date = c.fact_date
-JOIN olap.assets_dm AS ad
- ON ad.asset_id = c.asset_id
-WHERE
- c.disabled_at IS NULL AND NOT a.daily_subperiod_return IS NULL
+ SUM(b.daily_subperiod_return) AS daily_subperiod_return
+FROM insights.benchmark_twrr_by_asset_mv AS b
+INNER JOIN olap.assets_dm AS ad
+ ON ad.asset_id = b.asset_id
- c.benchmark_id,
- c.fact_date,
+ b.benchmark_id,
+ b.fact_date,
position_values_mv
materialized_view
view diff
- backfill_order=FIXED(olap.assets_dm -> insights.holding_values_mv, asset_service.foreign_exchange_rates_eod_ft -> insights.holding_values_mv)
+ backfill_order=FIXED(
+ olap.assets_dm -> insights.holding_values_mv,
+ asset_service.foreign_exchange_rates_eod_ft -> insights.holding_values_mv
+ )
+ atag.source_entity_type,
intraday_position_values_mv
materialized_view
view diff
-), pos_keys AS (
- SELECT
- account_id,
- asset_id
- FROM (
- SELECT
- account_id,
- asset_id
- FROM eod_base
- UNION ALL
- SELECT
- account_id,
- asset_id
- FROM insights.intraday_holdings_latest_mv
- )
- GROUP BY
- account_id,
- asset_id
- k.account_id,
- k.asset_id,
+ COALESCE(hl.account_id, e.account_id) AS account_id,
+ COALESCE(hl.asset_id, e.asset_id) AS asset_id,
- FROM pos_keys AS k
- LEFT JOIN insights.intraday_holdings_latest_mv AS hl
- ON hl.account_id = k.account_id AND hl.asset_id = k.asset_id
- LEFT JOIN eod_base AS e
- ON e.account_id = k.account_id AND e.asset_id = k.asset_id
- WHERE
- NOT hl.account_id IS NULL OR NOT e.account_id IS NULL
+ FROM insights.intraday_holdings_latest_mv AS hl
+ FULL OUTER JOIN eod_base AS e
+ ON e.account_id = hl.account_id AND e.asset_id = hl.asset_id