Notes

BMI constituent

WITH busdates as ( -- use S&P Global BMI Index (tradingItemId = 84367209 price return in USD) and Daily MTD Return(dataItemId = 114324) for calendar
--select max(valuedate) as valuedate from xf_target.BMIIndexMthlyNumValue where tradingItemId = 84367209 and valuedate between '2005-01-01' and '2024-06-30' group by DATE_TRUNC('month',valuedate) --'{{ start_date }}' and '{{ end_date }}'
-- BMIIndexMthlyNumValue has missing months and extra date on March 2013. Checked BMIIndexMthlyNumValue's business month end is aligned with msciindexmonthlyvalue's.
-- use MSCI Usa Index(tradingItemId = 380401081 price return in USD) and index value(dataItemId = 112099) for calendar
select distinct valuedate from msciindexmonthlyvalue where tradingItemId = 380401081 and dataItemId = 112099 and valuedate between '{{ start_date }}' and '{{ end_date }}' -- '2005-01-01' and '2024-06-30'
)

,bmiindex as (select
cal.valueDate :: DATE, i.indexName,ic.fromdate,ic.todate
, c2.securityid as xf_security_id
, c2.tradingitemid as xf_trading_item_id
, c2.constituentid
,row_number()
over (partition by DATE_TRUNC('month',cal.valuedate), c2.tradingitemid
order by cal.valuedate desc, (CASE WHEN lower(i.indexName) = 's&p global bmi index' THEN 1 ELSE 0 END) desc,
(CASE WHEN cal.valuedate between coalesce(ic.fromdate, '1900-01-01') and coalesce(ic.todate, '2099-01-01') THEN 1 ELSE 0 END) desc, --constituent changed in the middle of a month
coalesce(ic.todate, '2050-01-01')::date - coalesce(ic.fromdate, '1950-01-01')::date desc ) as rn
from xf_target.bmiindex i
inner join xf_target.bmiIndexConstituent ic
on i.indexid = ic.indexid
inner join xf_target.bmiconstituent c2
on ic.constituentid = c2.constituentid
and c2.securityid is not null
inner join busdates cal
on DATE_TRUNC('month',cal.valuedate) >= DATE_TRUNC('month',coalesce(ic.fromdate, '1900-01-01'))
and DATE_TRUNC('month',cal.valuedate) <= DATE_TRUNC('month',coalesce(ic.todate, '2099-01-01'))
where 1 = 1
and c2.tradingitemid is not null and i.indexfamily != 'SPDJ BMI Emerging'



) -- S&P BMI index family

--insert into qra_scratch.bmi_constituent_mappings(as_of_date, bmi_business_month_end, xf_trading_item_id, xf_security_id, xf_company_id, bmi_constituent, rnk)
select (date_trunc('month',bmi.valueDate) + interval '1 month - 1 day')::date as as_of_date,bmi.valueDate, xf_trading_item_id, xf_security_id, s.companyid as xf_company_id, bmi.constituentid as bmi_constituent, rn as rnk
from (
select c.valuedate, c.xf_trading_item_id, c.xf_security_id, c.constituentid, min(c.rn) as rn
from bmiindex c
where c.valuedate between '{{ start_date }}' and '{{ end_date }}' -- '2005-01-01' and '2024-06-30'
group by c.valuedate, c.xf_trading_item_id, c.xf_security_id, c.constituentid
) bmi
left join xf_target.ciqsecurity s on s.securityid = bmi.xf_security_id;


-- -- validate and make sure each tradingitemid is linked to one distinct constituent id
-- do $$ begin ASSERT (select count(distinct s.bmi_constituent)
-- from qra_scratch.bmi_constituent_mappings s
-- where rnk = 1
-- group by s.xf_trading_item_id, s.as_of_date
-- having count(distinct s.bmi_constituent) > 1) is null, 'more than one constituent is linked to a tradingitemid';end;$$;
-- -- validate all non-temporary constituents with not null tradingitemid and in a supported index are included.
-- do $$ begin ASSERT (select count(*)
-- from xf_target.bmiindex i
-- inner join xf_target.bmiIndexConstituent ic
-- on i.indexid = ic.indexid
-- left join xf_target.bmiconstituent bc on bc.constituentid = ic.constituentid
-- left join qra_scratch.bmi_constituent_mappings m
-- on m.bmi_constituent = ic.constituentid and m.bmi_business_month_end between coalesce(ic.fromdate, '1900-01-01') and coalesce(ic.todate, '2099-01-01')
-- where m.as_of_date is null and i.indexfamily != 'SPDJ BMI Emerging' and bc.tradingitemid is not null
-- and coalesce(ic.fromdate, '1950-01-01')::date <= '{{ end_date }}' and coalesce(ic.todate, '2050-01-01')::date >= '2005-01-31'
-- and (ic.todate is null or ic.todate::date - coalesce(ic.fromdate, '1950-01-01')::date > 31)) = 0, 'not all SP BMI constituents are included';end;$$;

-- -- validate all months in a year are included
-- do $$ begin ASSERT (select count(distinct as_of_date) -- ,date_part('year', m.as_of_date)
-- from qra_scratch.bmi_constituent_mappings m
-- inner join (select min(date_part('year', as_of_date)) as firstyear, max(date_part('year', as_of_date)) as lastyear from qra_scratch.bmi_constituent_mappings) years
-- on date_part('year', m.as_of_date) > firstyear and date_part('year', m.as_of_date) < lastyear
-- group by date_part('year', m.as_of_date)
-- having count(distinct as_of_date) != 12) is null, 'Not all months are included';end;$$;
-----------------------------------------------------