SELECT Internal identification start ex id ex id as experience_id ex s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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;