Notes

{% if as_of_date is defined or start is defined or end is defined %}

SELECT pos.portfolio_uid,
'{{ exposure_type }}' AS exposure_type,
pos.as_of_date,
NULL::TIMESTAMP AS as_of_timestamp,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
CASE WHEN (eg_asset_class.exposure_category IS NOT NULL
AND eg_country.exposure_category IS NOT NULL)
THEN concat(eg_asset_class.exposure_category, ' x ',
eg_country.exposure_category) END AS exposure_category,
{% if weight_calc == "clarity" %}
(sum(pos.total_mv_usd) + CASE WHEN (eg_asset_class.exposure_category IS NULL
OR eg_country.exposure_category IS NUll)
THEN max(pru.spendable_accruals_usd) ELSE 0.0 END)
/ max(pru.value_usd + pru.spendable_accruals_usd)::FLOAT AS weight,
max(pru.value_usd + pru.spendable_accruals_usd)::NUMERIC(28, 10) AS weight_denominator
{% elif weight_calc == "crd-lite" %}
(sum(pos.total_mv_usd) + CASE WHEN (eg_asset_class.exposure_category IS NULL
OR eg_country.exposure_category IS NUll)
THEN max(pru.accruals_usd) ELSE 0.0 END)
/ max(pru.value_usd + pru.accruals_usd + pru.variation_margin_usd) AS weight,
max(pru.value_usd + pru.accruals_usd + pru.variation_margin_usd)::NUMERIC(28, 10) AS weight_denominator
{% endif %}
{% else %}
eg.exposure_category AS exposure_category,
CASE WHEN '{{ exposure_type}}' = 'china_vie' AND eg.exposure_category = 'China VIE (Indirect)'
THEN 0.5
ELSE 1.0 END *
{% if weight_calc == "clarity" %}
(sum(pos.clean_mv_usd)
+ CASE WHEN eg.exposure_category IS NULL THEN max(pru.spendable_accruals_usd) ELSE 0.0 END)
/ max(pru.value_usd + pru.spendable_accruals_usd)::FLOAT AS weight,
max(pru.value_usd + pru.spendable_accruals_usd)::NUMERIC(28, 10) AS weight_denominator
{% elif weight_calc == "crd-lite" %}
(sum(pos.total_mv_usd)
+ CASE WHEN eg.exposure_category IS NULL THEN max(pru.accruals_usd) ELSE 0.0 END)
/ max(pru.value_usd + pru.accruals_usd + pru.variation_margin_usd)::FLOAT AS weight,
max(pru.value_usd + pru.accruals_usd + pru.variation_margin_usd)::NUMERIC(28, 10) AS weight_denominator
{% endif %}
{% endif %}
FROM xxxx_api.position_rollups pos
INNER JOIN xxxx_api.portfolio_rollups pru
ON pos.portfolio_uid = pru.portfolio_uid
AND pos.as_of_date = pru.as_of_date

-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
LEFT JOIN xxxx_api.asset_class_exposure_groups eg_asset_class
ON pos.listing_uid = eg_asset_class.listing_uid
AND pos.as_of_date <@ eg_asset_class.effective_during

LEFT JOIN xxxx_api.country_exposure_groups eg_country
ON pos.listing_uid = eg_country.listing_uid
AND pos.as_of_date <@ eg_country.effective_during
{% else %}
LEFT JOIN xxxx_api.{{ exposure_type }}_exposure_groups eg
ON pos.listing_uid = eg.listing_uid
AND pos.as_of_date <@ eg.effective_during
{% endif %}

-- TODO: eq_ex_us support once combo-exposure groups are available
{% for filter_exposure_type in 'asset_class', 'asset_subclass', 'china_vie', 'country',
'em', 'ex_us', 'ex_us_ca', 'industry', 'industry_group',
'region', 'sector', 'subindustry' %}
{% if context[filter_exposure_type] is defined %}
-- TODO: eq_ex_us support once combo-exposure groups are available
INNER JOIN xxxx_api.{{ filter_exposure_type }}_exposure_groups eg_{{ filter_exposure_type }}
ON pos.listing_uid = eg_{{ filter_exposure_type }}.listing_uid
AND pos.as_of_date <@ eg_{{ filter_exposure_type }}.effective_during
AND lower(eg_{{ filter_exposure_type }}.exposure_category)
= lower('{{ context[filter_exposure_type] }}')
{% endif %}
{% endfor %}

INNER JOIN codex.calendar cal
{% if frequency == "d" %}
ON pos.as_of_date = cal.calendar_date
AND cal.is_business_date
{% elif frequency == "w" %}
ON pos.as_of_date = cal.previous_business_date
AND extract(dow FROM cal.calendar_date) = 4
{% elif frequency == "m" %}
ON pos.as_of_date = cal.calendar_date
AND cal.calendar_date = cal.business_month_end
{% elif frequency == "q" %}
ON pos.as_of_date = cal.calendar_date
AND cal.calendar_date = cal.business_month_end
AND extract(month FROM cal.calendar_date) IN (3, 6, 9, 12)
{% elif frequency == "y" %}
ON pos.as_of_date = cal.calendar_date
AND cal.calendar_date = cal.business_month_end
AND extract(month FROM cal.calendar_date) = 12
{% else %}
ON 1 = 0 -- Return nothing on unknown frequency type
{% endif %}
WHERE pos.portfolio_uid = $1::BIGINT
AND pos.as_of_date BETWEEN '{{ start }}'::DATE AND '{{ end }}'::DATE
{% if weight_calc == "clarity" %}
AND (pru.value_usd + pru.spendable_accruals_usd) > 10.0
{% elif weight_calc == "crd-lite" %}
AND pru.value_usd > 10.0
{% endif %}
{% if exposure_category is defined %}
AND lower(eg.exposure_category) = lower('{{ exposure_category }}')
{% endif %}
GROUP BY pos.portfolio_uid,
pos.as_of_date,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
eg_asset_class.exposure_category,
eg_country.exposure_category
{% else %}
eg.exposure_category
{% endif %}
ORDER BY pos.portfolio_uid,
pos.as_of_date,
weight DESC,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
eg_asset_class.exposure_category,
eg_country.exposure_category
{% else %}
eg.exposure_category
{% endif %}

{% else %}

SELECT pos.portfolio_uid::BIGINT,
'{{ exposure_type }}' AS exposure_type,
pos.as_of_timestamp::DATE AS as_of_date,
pos.as_of_timestamp::TIMESTAMP AS as_of_timestamp,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
CASE WHEN (eg_asset_class.exposure_category IS NOT NULL
AND eg_country.exposure_category IS NOT NULL)
THEN concat(eg_asset_class.exposure_category, ' x ',
eg_country.exposure_category) END AS exposure_category,
(sum(pos.projected_mkt_val_usd) + CASE WHEN (eg_asset_class.exposure_category IS NULL
OR eg_country.exposure_category IS NUll)
THEN max(pru.portfolio_tna) ELSE 0.0 END)
/ max(pru.portfolio_tna) AS weight,
max(pru.portfolio_tna)::NUMERIC(28, 10) AS weight_denominator
{% else %}
eg.exposure_category AS exposure_category,
-- Projected positions only support 'crd-lite' weight calculations by design.
CASE WHEN '{{ exposure_type}}' = 'china_vie' AND eg.exposure_category = 'China VIE (Indirect)'
THEN 0.5
ELSE 1.0 END *
(sum(pos.projected_mkt_val_usd) + CASE WHEN eg.exposure_category IS NULL THEN 0.0 ELSE 0.0 END)
/ max(pru.portfolio_tna) AS weight,
max(pru.portfolio_tna)::NUMERIC(28, 10) AS weight_denominator
{% endif %}
FROM ois_cache.ois_projected_positions pos
INNER JOIN (SELECT portfolio_uid,
sum(projected_mkt_val_usd) portfolio_tna
FROM ois_cache.ois_projected_positions pos
WHERE pos.include_accruals = TRUE -- Denominator includes accruals
GROUP BY 1
HAVING sum(projected_mkt_val_usd) > 10.0) pru
ON pos.portfolio_uid = pru.portfolio_uid

-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
LEFT JOIN xxxx_api.asset_class_exposure_groups eg_asset_class
ON pos.listing_uid = eg_asset_class.listing_uid
AND pos.as_of_timestamp::DATE <@ eg_asset_class.effective_during

LEFT JOIN xxxx_api.country_exposure_groups eg_country
ON pos.listing_uid = eg_country.listing_uid
AND pos.as_of_timestamp::DATE <@ eg_country.effective_during
{% else %}
LEFT JOIN xxxx_api.{{ exposure_type }}_exposure_groups eg
ON pos.listing_uid = eg.listing_uid
AND pos.as_of_timestamp::DATE <@ eg.effective_during
{% endif %}

{% for filter_exposure_type in 'asset_class', 'asset_subclass', 'china_vie', 'country',
'em', 'ex_us', 'ex_us_ca', 'industry', 'industry_group',
'region', 'sector', 'subindustry' %}
{% if context[filter_exposure_type] is defined %}
-- TODO: eq_ex_us support once combo-exposure groups are available
INNER JOIN xxxx_api.{{ filter_exposure_type }}_exposure_groups eg_{{ filter_exposure_type }}
ON pos.listing_uid = eg_{{ filter_exposure_type }}.listing_uid
AND upper(eg_{{ filter_exposure_type }}.effective_during) IS NULL
AND lower(eg_{{ filter_exposure_type }}.exposure_category)
= lower('{{ context[filter_exposure_type] }}')
{% endif %}
{% endfor %}
WHERE pos.portfolio_uid = $1::BIGINT
AND pos.include_accruals = FALSE -- Numerator excludes accruals
{% if exposure_category is defined %}
AND lower(eg.exposure_category) = lower('{{ exposure_category }}')
{% endif %}
GROUP BY pos.portfolio_uid,
pos.as_of_timestamp,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
eg_asset_class.exposure_category,
eg_country.exposure_category
{% else %}
eg.exposure_category
{% endif %}
ORDER BY pos.portfolio_uid,
pos.as_of_timestamp,
weight DESC,
-- TODO: eq_ex_us support once combo-exposure groups are available
{% if exposure_type == 'eq_ex_us' %}
eg_asset_class.exposure_category,
eg_country.exposure_category
{% else %}
eg.exposure_category
{% endif %}

{% endif %}