Notes

{% if id_type == 'indexname' %} -- FYYM: should this be only around i.indexname ?
select distinct
qu.as_of_date
{% if need_msci_mapping %}
,mi.indexname
, mcp.msci_business_month_end
,qu.float_marketcap_{{ index_weight_fif_dif }}_usd / sum(qu.float_marketcap_{{ index_weight_fif_dif }}_usd) OVER (PARTITION BY qu.as_of_date, mi.indexname) * 100 AS indexweight
{% endif %}
{% if need_russell_mapping %}
,ri.indexname
, rcp.russell_business_month_end
,qu.float_marketcap_{{ index_weight_fif_dif }}_usd / sum(qu.float_marketcap_{{ index_weight_fif_dif }}_usd) OVER (PARTITION BY qu.as_of_date, ri.indexname) * 100 AS indexweight
{% endif %}
{% if need_sp_mapping %}
,spi.indexname
, spcp.sp_business_month_end
,qu.float_marketcap_{{ index_weight_fif_dif }}_usd / sum(qu.float_marketcap_{{ index_weight_fif_dif }}_usd) OVER (PARTITION BY qu.as_of_date, spi.indexname) * 100 AS indexweight
{% endif %}

, qu.xf_company_id
, qu.xf_security_id
, qu.xf_trading_item_id
, qu.gvkey
, qu.iid
, qu.barra_id
{% for company_col in ciqcompany_col_list %}
, cc.{{ company_col}}
{% endfor %}

{% for company_col in company_locations_col_list %}
, cl.{{ company_col}}
{% endfor %}
, qu.sector as gics_sector_code
, qu.industry_grp as gics_industry_group_code
, qu.industry as gics_industry_code
-- , qu.su_bindustry as gics_subindustry_code --CONSUMG: column doesn't exists. FYYM: it is qu.su_bindustry
, sector.gicdesc as gics_sector
, industry_grp.gicdesc as gics_industry_group
, industry.gicdesc as gics_industry
, subindustry.gicdesc as gics_subindustry ----CONSUMG: column doesn't exists.
{% if company_marketcaps_col_list %}
, qu.float_marketcap_dif_usd
, qu.float_marketcap_fif_usd
{% for company_col in company_marketcaps_col_list %}
, cm.{{ company_col}}
{% endfor %}
{% endif %}


{% for mnth_ret_col in qra_universe_security_monthly_return_col_list %}
, sdr.{{ mnth_ret_col}}
{% endfor %}

{% for msci_fe_col in msci_facs_exposures_col_list %}
, msci_fe.{{ msci_fe_col}}
{% endfor %}
{% for msci_ge_col in msci_gemlt_exposures_col_list %}
, msci_ge.{{ msci_ge_col}}
{% endfor %}

{% for dict in xf_target_mscisecuritymonthlyvalue_list %}
{% for dataitemid_val, alias in dict.items() %}
, CASE WHEN {{ alias }}.dataitemid = {{ dataitemid_val }} THEN {{ alias }}.value END as {{ alias }}
{% endfor %}
{% endfor %}
{% for dict in xf_target_ciqafvaluemonthlyna_list %}
{% for factorid, alias in dict.items() %}
, CASE WHEN {{ alias }}.factorid = {{ factorid }} THEN {{ alias }}.factorvalue END as {{ alias.split('_')[-1] }}
{% endfor %}
{% endfor %}

from qra_scratch.qra_universe qu


{% if need_msci_mapping %}
LEFT JOIN qra_scratch.msci_constituent_mappings mcp
ON mcp.as_of_date = qu.as_of_date
AND mcp.msci_constituent = qu.msci_constituent

-- FYYM: this should only be done if we specify "indexname" and an MSCI index, the mapping might be required if we need e.g. MSCI z-scores for constituents of a Russell index
-- select index constituents
INNER JOIN (select distinct i0.indexname,
mic0.constituentid,
mcp0.msci_business_month_end
from xf_target.msciindex i0
inner join xf_target.msciindexconstituent mic0
on i0.indexid = mic0.indexid
AND i0.indexmodulecode = mic0.indexmodulecode
inner join qra_scratch.msci_constituent_mappings mcp0
on mcp0.msci_constituent = mic0.constituentid
AND
mcp0.msci_business_month_end BETWEEN coalesce(mic0.fromdate, '1950-01-01') AND coalesce(mic0.todate, '2050-01-01')
-- INNER JOIN ciqIndexConstituentValue v
-- ON i0.indexid = v.indexid
-- AND mic0.constituentID = v.constituentID
-- AND v.dataItemID = 112119 --Weights
-- AND v.valueDate = mcp0.msci_business_month_end

where LOWER(i0.indexname) = '{{ msci_index_name }}' ) mi
ON mi.constituentid = qu.msci_constituent AND mcp.msci_business_month_end = mi.msci_business_month_end
{% endif %}

{%if need_russell_mapping %}
-- -- add Russell mapping
-- LEFT JOIN qra_scratch.russell_constituent_mappings rcp
-- ON rcp.as_of_date = qu.as_of_date
-- AND rcp.russell_constituent = qu.russell_constituent
-- -- FYYM: same here, this should only be done if we specify "indexname" and a Russell index
-- -- select index constituents
-- INNER JOIN xf_target.ciqindexconstituent ric
-- ON ric.constituentid = qu.russell_constituent
-- AND
-- rcp.russell_business_month_end BETWEEN coalesce(ric.fromdate, '1950-01-01') AND coalesce(ric.todate, '2050-01-01')

-- INNER JOIN xf_target.ciqindex ri
-- ON ri.indexid = ric.indexid AND LOWER(ri.indexname) = '{{ russell_index_name }}'
-- -- where LOWER(i.indexname) = '{{ russell_index_name }}'
-- add Russell mapping
LEFT JOIN qra_scratch.russell_constituent_mappings rcp
ON rcp.as_of_date = qu.as_of_date
AND rcp.russell_constituent = qu.russell_constituent
-- select index constituents
INNER JOIN (select distinct i0.indexname,
mic0.constituentid,
mcp0.russell_business_month_end,
v.value as indexweight
from xf_target.ciqindex i0
inner join xf_target.ciqindexconstituent mic0
on i0.indexid = mic0.indexid
inner join qra_scratch.russell_constituent_mappings mcp0
on mcp0.russell_constituent = mic0.constituentid
AND
mcp0.russell_business_month_end >= '2024-01-01'
AND
mcp0.russell_business_month_end BETWEEN coalesce(mic0.fromdate, '1950-01-01') AND coalesce(mic0.todate, '2050-01-01')
-- FYYM: this is adding index weights, I think we can add it by default in every query that want index constituents
INNER JOIN ciqIndexConstituentValue v
ON i0.indexid = v.indexid
AND mic0.constituentID = v.constituentID
AND v.dataItemID = 112119 --Weights
AND v.valueDate = mcp0.russell_business_month_end
where LOWER(i0.indexname) = '{{ russell_index_name }}') ri
ON ri.constituentid = qu.russell_constituent AND
rcp.russell_business_month_end = ri.russell_business_month_end
{% endif %}

{% if need_sp_mapping %}
-- add S&P mapping
LEFT JOIN qra_scratch.spindex_constituent_mappings spcp
ON spcp.as_of_date = qu.as_of_date
AND spcp.sp_constituent = qu.sp_constituent
-- FYYM: same here, this should only be done if we specify "indexname" and am S&P index
-- select index constituents
-- INNER JOIN xf_target.ciqindexconstituent spic
-- ON spic.constituentid = qu.sp_constituent
-- AND
-- spcp.sp_business_month_end BETWEEN coalesce(spic.fromdate, '1950-01-01') AND coalesce(spic.todate, '2050-01-01')
-- INNER JOIN xf_target.ciqindex spi
-- ON spi.indexid = spic.indexid AND LOWER(spi.indexname) = '{{ sp_index_name }}'
-- -- where LOWER(spi.indexname) = '{{ sp_index_name }}'
INNER JOIN (select distinct i0.indexname,
mic0.constituentid,
mcp0.sp_business_month_end,
v.value as indexweight
from xf_target.ciqindex i0
inner join xf_target.ciqindexconstituent mic0
on i0.indexid = mic0.indexid
inner join qra_scratch.spindex_constituent_mappings mcp0
on mcp0.sp_constituent = mic0.constituentid
AND
mcp0.sp_business_month_end >= '2024-01-01'
AND
mcp0.sp_business_month_end BETWEEN coalesce(mic0.fromdate, '1950-01-01') AND coalesce(mic0.todate, '2050-01-01')
-- FYYM: this is adding index weights, I think we can add it by default in every query that want index constituents
INNER JOIN ciqIndexConstituentValue v
ON i0.indexid = v.indexid
AND mic0.constituentID = v.constituentID
AND v.dataItemID = 112119 --Weights
AND v.valueDate = mcp0.sp_business_month_end
where LOWER(i0.indexname) = '{{ sp_index_name }}') spi
ON spi.constituentid = qu.russell_constituent AND
spcp.sp_business_month_end = spi.sp_business_month_end
{% endif %}


-- add market cap if specified
{% if company_marketcaps_col_list %}
LEFT JOIN qra_scratch.company_marketcaps cm
ON qu.as_of_date = cm.as_of_date
AND qu.xf_company_id = cm.xf_company_id
{% endif %}
-- add company name if specified
{% if ciqcompany_col_list %}
LEFT JOIN xf_target.ciqcompany cc
ON cc.companyid = qu.xf_company_id
{% endif %}
-- add company location if specified
{% if company_locations_col_list %}
LEFT JOIN qra_scratch.company_locations cl
ON cl.as_of_date = qu.as_of_date
AND cl.xf_company_id = qu.xf_company_id
AND cl.gvkey = qu.gvkey
{% endif %}
-- add GICS sector, industry and industry group names if specified (this is the string name, the code is already in the qu table)
LEFT JOIN r_giccd sector
ON qu.sector = sector.giccd
LEFT JOIN r_giccd industry
ON qu.industry = industry.giccd
LEFT JOIN r_giccd subindustry
ON qu.sub_industry = subindustry.giccd
LEFT JOIN r_giccd industry_grp
ON qu.industry = industry_grp.giccd
-- add MSCI FaCS and GEM LT exposures if specified
{% if msci_facs_exposures_col_list %}
INNER JOIN msci.facs_exposures msci_fe
ON qu.barra_id = msci_fe.barra_id
AND mcp.msci_business_month_end = msci_fe.as_of_date
{% endif %}
{% if msci_gemlt_exposures_col_list %}
INNER JOIN msci.gemlt_exposures msci_ge
ON qu.barra_id = msci_ge.barra_id
AND mcp.msci_business_month_end = msci_ge.as_of_date
{% endif %}
-- add monthly return if specified
{% if qra_universe_security_monthly_return_col_list %}
left join qra_scratch.qra_universe_security_monthly_return sdr
on qu.xf_trading_item_id = sdr.xf_trading_item_id
and qu.as_of_date = sdr.as_of_date
{% endif %}

-- FYYM: I think this won't work if we have multiple keys, we need to loop over the list, not add them all at once
{% if xf_target_mscisecuritymonthlyvalue_list %}
{% for dict in xf_target_mscisecuritymonthlyvalue_list %}
{% for key, value in dict.items() %}
LEFT JOIN xf_target.mscisecuritymonthlyvalue {{ value }}
ON {{ value }}.valuedate = mcp.msci_business_month_end
AND {{ value }}.constituentid = qu.msci_constituent
AND {{ value }}.dataitemid = '{{ key }}'
{% endfor %}
{% endfor %}
{% endif %}

{% if xf_target_ciqafvaluemonthlyna_list %}
{% for dict in xf_target_ciqafvaluemonthlyna_list %}
{% for key, value in dict.items() %}
LEFT JOIN xf_target.ciqafvaluemonthlyna {{ value }}
ON {{ value }}.asofdate = qu.as_of_date
AND {{ value }}.gvkey = qu.gvkey
AND {{ value }}.iid = qu.iid
AND {{ value }}.factorid = {{ key }}
{% endfor %}
{% endfor %}
{% endif %}


WHERE 1 = 1
{% if start_date and end_date %}
AND qu.as_of_date between '{{ start_date }}' and '{{ end_date }}'
{% endif %}

{% elif id_type == 'xf_company_id' %}
SELECT *
FROM qra_scratch.qra_universe qu
WHERE qu.xf_company_id IN ({{ "'" + ids_list|join("', '") + "'" }})
{% if start_date and end_date %}
AND qu.as_of_date between '{{ start_date }}' and '{{ end_date }}'
{% endif %}
-- FYYM: here it'd need to be a little bit more flexible we we may want securities in a country + in specific sectors + etc
{% elif id_type == 'country'%}
SELECT *
FROM qra_scratch.qra_universe qu
-- add company location
LEFT JOIN qra_scratch.company_locations cl
ON cl.as_of_date = qu.as_of_date
AND cl.xf_company_id = qu.xf_company_id
AND cl.gvkey = qu.gvkey
-- GICS sector, industry and industry group names
{% if filter_with_conditions_dict %}
LEFT JOIN r_giccd sector
ON qu.sector = sector.giccd
LEFT JOIN r_giccd industry
ON qu.industry = industry.giccd
LEFT JOIN r_giccd subindustry
ON qu.sub_industry = subindustry.giccd
LEFT JOIN r_giccd industry_grp
ON qu.industry = industry_grp.giccd
{% if 'qra_marketcap_usd' in filter_with_conditions_dict.keys() %}
LEFT JOIN qra_scratch.company_marketcaps cm
ON qu.xf_company_id = cm.xf_company_id
{% endif %}
{% endif %}
WHERE cl.qra_country IN ({{ "'" + ids_list|join("', '") + "'" }})
{% for filter_key, filter_value in filter_with_conditions_dict.items() %}
{% if filter_key == "gics_sector" and filter_value.get('condition') == 'in' %}
AND sector.gicdesc IN ({{ "'" + filter_value.get('values')|join("', '") + "'" }})
{% elif filter_key == "gics_sector" and filter_value.get('condition') == '=' %}
AND sector.gicdesc = '{{ filter_value.get('values')[0] }}'
{% elif filter_key == "qra_marketcap_usd" and filter_value.get('condition') == 'in' %}
AND cm.qra_marketcap_usd IN ({{ "'" + filter_value.get('values')|join("', '") + "'" }})
{% elif filter_key == "qra_marketcap_usd" and filter_value.get('condition') in ('>', '<', '=', '<=', '>=' )%}
AND cm.qra_marketcap_usd {{ filter_value.get('condition') }} '{{ filter_value.get('values')[0] }}'
{% endif %}
{% endfor %}
{% if start_date and end_date %}
AND qu.as_of_date between '{{ start_date }}' and '{{ end_date }}'
{% endif %}
{% elif id_type == 'region'%}
SELECT *
FROM qra_scratch.qra_universe qu
-- add company location
LEFT JOIN qra_scratch.company_locations cl
ON cl.as_of_date = qu.as_of_date
AND cl.xf_company_id = qu.xf_company_id
AND cl.gvkey = qu.gvkey
WHERE cl.msci_qra_region = IN ({{ "'" + ids_list|join("', '") + "'" }})
{% if start_date and end_date %}
AND qu.as_of_date between '{{ start_date }}' and '{{ end_date }}'
{% endif %}
{% elif id_type == 'gvkey' %}
SELECT *
FROM qra_scratch.qra_universe qu
WHERE qu.gvkey IN ({{ "'" + ids_list|join("', '") + "'" }});

{% elif id_type == 'xf_trading_item_id' %}
SELECT *
FROM qra_scratch.qra_universe qu
WHERE qu.xf_trading_item_id IN ({{ "'" + ids_list|join("', '") + "'" }});

{% elif id_type == 'xf_security_id' %}
SELECT *
FROM qra_scratch.qra_universe qu
WHERE qu.xf_security_id IN ({{ "'" + ids_list|join("', '") + "'" }});
{% endif %}