select distinct
b.source_factset_entity_id
, m.entity_proper_name
, m.iso_country
, b.target_factset_entity_id
, n.entity_proper_name
, n.iso_country
, s.level_2_co_id
, o.entity_proper_name
, o.iso_country into source_factset_entity_id, source_entity_name, source_entity_country, level_1_customer_id, level_1_customer_name, level_1_customer_country, level_2_customer_id, level_2_customer_name, level_2_customer_country
from ent_v1_ent_scr_relationships b
join (select distinct source_factset_entity_id, target_factset_entity_id as level_2_co_id,revenue_pct from ent_v1.ent_scr_relationships where rel_type = 'customer' and end_date is null)
s on b.target_factset_entity_id = s.source_factset_entity_id
join sym_v1.sym_entity m on b.source_factset_entity_id = m.factset_entity_id
join sym_v1.sym_entity n on b.target_factset_entity_id = n.factset_entity_id
join sym_v1.sym_entity o on s.level_2_co_id = o.factset_entity_id
where b.end_date is null
and b.rel_type = 'customer';
|