SELECT
/* Internal identification start */
ex.id,
ex.id as experience_id,
ex.source,
ex.employee_id,
ex.sb_updated,
/* Internal identification end */
/* Employee specific start */
emp.highest_education,
emp.total_experience,
LOWER(emp.gender_calculated) as gender_calculated,
/* Employee specific end */
/* Job specific start */
ex.onet_l5,
ex.onet_l6,
ex.sbji_wi,
SUBSTRING_INDEX(ex.sbji_wi, "_", 1) as sbji_wi_wa,
ex.l2_code,
sbji_l3s.cluster_id as l3_cluster_id,
ex.l4_code,
sbji_l4s.l4 as sbji_l4,
sbji_l4s.cluster_id as l4_cluster_id,
sbji_l4_clusters.name as l4_cluster,
ex.l5_code,
sbji_l5s.l5 as sbji_l5,
ex.current_position,
/* Job specific end */
/* Company and industry specific start */
CONCAT(ex.company, "|", ex.company_user_input , "|", ex.company_id) as company_aggr,
ex.company_user_input,
ex.company_id,
ex.company,
ex.industry_id ,
/* Company and industry specific end */
/* Location specific end */
ex.sbgi_l3,
ex.sbgi_l2,
/* Location specific end */
/* Duration specific start */
ex.start_month,
ex.start_year,
ex.end_month,
ex.end_year,
ex.duration,
/* Duration specific end */
/* Skills specific start */
employee_sbsi.sbsi_id,
employee_sbsi.skill,
sbsi.sbsi_l1,
sbsi.sbsi_l2,
sbsi.sbsi_l3
/* Skills specific end */
FROM
experiences_2 ex
left join employees emp on
emp.id = ex.employee_id
left join sbji_l3s on
sbji_l3s.l3_code = ex.l3_code
left join sbji_l4s on
sbji_l4s.l4_code = ex.l4_code
left join sbji_l5s on
sbji_l5s.l5_code = ex.l5_code
left join sbji_l4_clusters on
sbji_l4_clusters.id = sbji_l4s.cluster_id
left join employee_sbsi on
employee_sbsi.employee_id = emp.id
left join sbsi on
sbsi.id = employee_sbsi.sbsi_id
where
ex.instance = 'UK'
limit 1000;