-
Notifications
You must be signed in to change notification settings - Fork 47
/
Copy pathsubregion-fast-fcp.sql
255 lines (254 loc) · 37.9 KB
/
subregion-fast-fcp.sql
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
#standardSQL
# Create a `countries` alias that decorates each dataset with geographic metadata.
# Generated by utils/countries.js.
# This query consumes 72.4 GB.
WITH
countries AS (
SELECT *, 'af' AS country_code, 'Afghanistan' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_af.201902` UNION ALL
SELECT *, 'ax' AS country_code, 'Åland Islands' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_ax.201902` UNION ALL
SELECT *, 'al' AS country_code, 'Albania' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_al.201902` UNION ALL
SELECT *, 'dz' AS country_code, 'Algeria' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_dz.201902` UNION ALL
SELECT *, 'as' AS country_code, 'American Samoa' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_as.201902` UNION ALL
SELECT *, 'ad' AS country_code, 'Andorra' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_ad.201902` UNION ALL
SELECT *, 'ao' AS country_code, 'Angola' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_ao.201902` UNION ALL
SELECT *, 'ai' AS country_code, 'Anguilla' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_ai.201902` UNION ALL
SELECT *, 'ag' AS country_code, 'Antigua and Barbuda' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_ag.201902` UNION ALL
SELECT *, 'ar' AS country_code, 'Argentina' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_ar.201902` UNION ALL
SELECT *, 'am' AS country_code, 'Armenia' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_am.201902` UNION ALL
SELECT *, 'aw' AS country_code, 'Aruba' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_aw.201902` UNION ALL
SELECT *, 'au' AS country_code, 'Australia' AS country, 'Oceania' AS region, 'Australia and New Zealand' AS subregion FROM `chrome-ux-report.country_au.201902` UNION ALL
SELECT *, 'at' AS country_code, 'Austria' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_at.201902` UNION ALL
SELECT *, 'az' AS country_code, 'Azerbaijan' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_az.201902` UNION ALL
SELECT *, 'bs' AS country_code, 'Bahamas' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_bs.201902` UNION ALL
SELECT *, 'bh' AS country_code, 'Bahrain' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_bh.201902` UNION ALL
SELECT *, 'bd' AS country_code, 'Bangladesh' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_bd.201902` UNION ALL
SELECT *, 'bb' AS country_code, 'Barbados' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_bb.201902` UNION ALL
SELECT *, 'by' AS country_code, 'Belarus' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_by.201902` UNION ALL
SELECT *, 'be' AS country_code, 'Belgium' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_be.201902` UNION ALL
SELECT *, 'bz' AS country_code, 'Belize' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_bz.201902` UNION ALL
SELECT *, 'bj' AS country_code, 'Benin' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_bj.201902` UNION ALL
SELECT *, 'bm' AS country_code, 'Bermuda' AS country, 'Americas' AS region, 'Northern America' AS subregion FROM `chrome-ux-report.country_bm.201902` UNION ALL
SELECT *, 'bt' AS country_code, 'Bhutan' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_bt.201902` UNION ALL
SELECT *, 'bo' AS country_code, 'Bolivia (Plurinational State of)' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_bo.201902` UNION ALL
SELECT *, 'bq' AS country_code, 'Bonaire, Sint Eustatius and Saba' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_bq.201902` UNION ALL
SELECT *, 'ba' AS country_code, 'Bosnia and Herzegovina' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_ba.201902` UNION ALL
SELECT *, 'bw' AS country_code, 'Botswana' AS country, 'Africa' AS region, 'Southern Africa' AS subregion FROM `chrome-ux-report.country_bw.201902` UNION ALL
SELECT *, 'br' AS country_code, 'Brazil' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_br.201902` UNION ALL
SELECT *, 'io' AS country_code, 'British Indian Ocean Territory' AS country, '' AS region, 'null' AS subregion FROM `chrome-ux-report.country_io.201902` UNION ALL
SELECT *, 'bn' AS country_code, 'Brunei Darussalam' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_bn.201902` UNION ALL
SELECT *, 'bg' AS country_code, 'Kosovo' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_bg.201902` UNION ALL
SELECT *, 'bf' AS country_code, 'Burkina Faso' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_bf.201902` UNION ALL
SELECT *, 'bi' AS country_code, 'Burundi' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_bi.201902` UNION ALL
SELECT *, 'kh' AS country_code, 'Cambodia' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_kh.201902` UNION ALL
SELECT *, 'cm' AS country_code, 'Cameroon' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_cm.201902` UNION ALL
SELECT *, 'ca' AS country_code, 'Canada' AS country, 'Americas' AS region, 'Northern America' AS subregion FROM `chrome-ux-report.country_ca.201902` UNION ALL
SELECT *, 'cv' AS country_code, 'Cabo Verde' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_cv.201902` UNION ALL
SELECT *, 'ky' AS country_code, 'Cayman Islands' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_ky.201902` UNION ALL
SELECT *, 'cf' AS country_code, 'Central African Republic' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_cf.201902` UNION ALL
SELECT *, 'td' AS country_code, 'Chad' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_td.201902` UNION ALL
SELECT *, 'cl' AS country_code, 'Chile' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_cl.201902` UNION ALL
SELECT *, 'cn' AS country_code, 'China' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_cn.201902` UNION ALL
SELECT *, 'cx' AS country_code, 'Christmas Island' AS country, '' AS region, 'null' AS subregion FROM `chrome-ux-report.country_cx.201902` UNION ALL
SELECT *, 'co' AS country_code, 'Colombia' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_co.201902` UNION ALL
SELECT *, 'km' AS country_code, 'Comoros' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_km.201902` UNION ALL
SELECT *, 'cg' AS country_code, 'Congo' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_cg.201902` UNION ALL
SELECT *, 'cd' AS country_code, 'Congo (Democratic Republic of the)' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_cd.201902` UNION ALL
SELECT *, 'ck' AS country_code, 'Cook Islands' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_ck.201902` UNION ALL
SELECT *, 'cr' AS country_code, 'Costa Rica' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_cr.201902` UNION ALL
SELECT *, 'ci' AS country_code, 'Côte d\'Ivoire' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_ci.201902` UNION ALL
SELECT *, 'hr' AS country_code, 'Croatia' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_hr.201902` UNION ALL
SELECT *, 'cu' AS country_code, 'Cuba' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_cu.201902` UNION ALL
SELECT *, 'cw' AS country_code, 'Curaçao' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_cw.201902` UNION ALL
SELECT *, 'cy' AS country_code, 'Cyprus' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_cy.201902` UNION ALL
SELECT *, 'cz' AS country_code, 'Czech Republic' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_cz.201902` UNION ALL
SELECT *, 'dk' AS country_code, 'Denmark' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_dk.201902` UNION ALL
SELECT *, 'dj' AS country_code, 'Djibouti' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_dj.201902` UNION ALL
SELECT *, 'dm' AS country_code, 'Dominica' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_dm.201902` UNION ALL
SELECT *, 'do' AS country_code, 'Dominican Republic' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_do.201902` UNION ALL
SELECT *, 'ec' AS country_code, 'Ecuador' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_ec.201902` UNION ALL
SELECT *, 'eg' AS country_code, 'Egypt' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_eg.201902` UNION ALL
SELECT *, 'sv' AS country_code, 'El Salvador' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_sv.201902` UNION ALL
SELECT *, 'gq' AS country_code, 'Equatorial Guinea' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_gq.201902` UNION ALL
SELECT *, 'er' AS country_code, 'Eritrea' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_er.201902` UNION ALL
SELECT *, 'ee' AS country_code, 'Estonia' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_ee.201902` UNION ALL
SELECT *, 'et' AS country_code, 'Ethiopia' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_et.201902` UNION ALL
SELECT *, 'fk' AS country_code, 'Falkland Islands (Malvinas)' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_fk.201902` UNION ALL
SELECT *, 'fo' AS country_code, 'Faroe Islands' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_fo.201902` UNION ALL
SELECT *, 'fj' AS country_code, 'Fiji' AS country, 'Oceania' AS region, 'Melanesia' AS subregion FROM `chrome-ux-report.country_fj.201902` UNION ALL
SELECT *, 'fi' AS country_code, 'Finland' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_fi.201902` UNION ALL
SELECT *, 'fr' AS country_code, 'France' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_fr.201902` UNION ALL
SELECT *, 'gf' AS country_code, 'French Guiana' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_gf.201902` UNION ALL
SELECT *, 'pf' AS country_code, 'French Polynesia' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_pf.201902` UNION ALL
SELECT *, 'ga' AS country_code, 'Gabon' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_ga.201902` UNION ALL
SELECT *, 'gm' AS country_code, 'Gambia' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_gm.201902` UNION ALL
SELECT *, 'ge' AS country_code, 'Georgia' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_ge.201902` UNION ALL
SELECT *, 'de' AS country_code, 'Germany' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_de.201902` UNION ALL
SELECT *, 'gh' AS country_code, 'Ghana' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_gh.201902` UNION ALL
SELECT *, 'gi' AS country_code, 'Gibraltar' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_gi.201902` UNION ALL
SELECT *, 'gr' AS country_code, 'Greece' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_gr.201902` UNION ALL
SELECT *, 'gl' AS country_code, 'Greenland' AS country, 'Americas' AS region, 'Northern America' AS subregion FROM `chrome-ux-report.country_gl.201902` UNION ALL
SELECT *, 'gd' AS country_code, 'Grenada' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_gd.201902` UNION ALL
SELECT *, 'gp' AS country_code, 'Guadeloupe' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_gp.201902` UNION ALL
SELECT *, 'gu' AS country_code, 'Guam' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_gu.201902` UNION ALL
SELECT *, 'gt' AS country_code, 'Guatemala' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_gt.201902` UNION ALL
SELECT *, 'gg' AS country_code, 'Guernsey' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_gg.201902` UNION ALL
SELECT *, 'gn' AS country_code, 'Guinea' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_gn.201902` UNION ALL
SELECT *, 'gw' AS country_code, 'Guinea-Bissau' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_gw.201902` UNION ALL
SELECT *, 'gy' AS country_code, 'Guyana' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_gy.201902` UNION ALL
SELECT *, 'ht' AS country_code, 'Haiti' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_ht.201902` UNION ALL
SELECT *, 'hn' AS country_code, 'Honduras' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_hn.201902` UNION ALL
SELECT *, 'hk' AS country_code, 'Hong Kong' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_hk.201902` UNION ALL
SELECT *, 'hu' AS country_code, 'Hungary' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_hu.201902` UNION ALL
SELECT *, 'is' AS country_code, 'Iceland' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_is.201902` UNION ALL
SELECT *, 'in' AS country_code, 'India' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_in.201902` UNION ALL
SELECT *, 'id' AS country_code, 'Indonesia' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_id.201902` UNION ALL
SELECT *, 'ir' AS country_code, 'Iran (Islamic Republic of)' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_ir.201902` UNION ALL
SELECT *, 'iq' AS country_code, 'Iraq' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_iq.201902` UNION ALL
SELECT *, 'ie' AS country_code, 'Ireland' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_ie.201902` UNION ALL
SELECT *, 'im' AS country_code, 'Isle of Man' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_im.201902` UNION ALL
SELECT *, 'il' AS country_code, 'Israel' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_il.201902` UNION ALL
SELECT *, 'it' AS country_code, 'Italy' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_it.201902` UNION ALL
SELECT *, 'jm' AS country_code, 'Jamaica' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_jm.201902` UNION ALL
SELECT *, 'jp' AS country_code, 'Japan' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_jp.201902` UNION ALL
SELECT *, 'je' AS country_code, 'Jersey' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_je.201902` UNION ALL
SELECT *, 'jo' AS country_code, 'Jordan' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_jo.201902` UNION ALL
SELECT *, 'kz' AS country_code, 'Kazakhstan' AS country, 'Asia' AS region, 'Central Asia' AS subregion FROM `chrome-ux-report.country_kz.201902` UNION ALL
SELECT *, 'ke' AS country_code, 'Kenya' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_ke.201902` UNION ALL
SELECT *, 'ki' AS country_code, 'Kiribati' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_ki.201902` UNION ALL
SELECT *, 'kp' AS country_code, 'Korea (Democratic People\'s Republic of)' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_kp.201902` UNION ALL
SELECT *, 'kr' AS country_code, 'Korea (Republic of)' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_kr.201902` UNION ALL
SELECT *, 'kw' AS country_code, 'Kuwait' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_kw.201902` UNION ALL
SELECT *, 'kg' AS country_code, 'Kyrgyzstan' AS country, 'Asia' AS region, 'Central Asia' AS subregion FROM `chrome-ux-report.country_kg.201902` UNION ALL
SELECT *, 'la' AS country_code, 'Lao People\'s Democratic Republic' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_la.201902` UNION ALL
SELECT *, 'lv' AS country_code, 'Latvia' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_lv.201902` UNION ALL
SELECT *, 'lb' AS country_code, 'Lebanon' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_lb.201902` UNION ALL
SELECT *, 'ls' AS country_code, 'Lesotho' AS country, 'Africa' AS region, 'Southern Africa' AS subregion FROM `chrome-ux-report.country_ls.201902` UNION ALL
SELECT *, 'lr' AS country_code, 'Liberia' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_lr.201902` UNION ALL
SELECT *, 'ly' AS country_code, 'Libya' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_ly.201902` UNION ALL
SELECT *, 'li' AS country_code, 'Liechtenstein' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_li.201902` UNION ALL
SELECT *, 'lt' AS country_code, 'Lithuania' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_lt.201902` UNION ALL
SELECT *, 'lu' AS country_code, 'Luxembourg' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_lu.201902` UNION ALL
SELECT *, 'mo' AS country_code, 'Macao' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_mo.201902` UNION ALL
SELECT *, 'mk' AS country_code, 'Macedonia (the former Yugoslav Republic of)' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_mk.201902` UNION ALL
SELECT *, 'mg' AS country_code, 'Madagascar' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_mg.201902` UNION ALL
SELECT *, 'mw' AS country_code, 'Malawi' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_mw.201902` UNION ALL
SELECT *, 'my' AS country_code, 'Malaysia' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_my.201902` UNION ALL
SELECT *, 'mv' AS country_code, 'Maldives' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_mv.201902` UNION ALL
SELECT *, 'ml' AS country_code, 'Mali' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_ml.201902` UNION ALL
SELECT *, 'mt' AS country_code, 'Malta' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_mt.201902` UNION ALL
SELECT *, 'mh' AS country_code, 'Marshall Islands' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_mh.201902` UNION ALL
SELECT *, 'mq' AS country_code, 'Martinique' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_mq.201902` UNION ALL
SELECT *, 'mr' AS country_code, 'Mauritania' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_mr.201902` UNION ALL
SELECT *, 'mu' AS country_code, 'Mauritius' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_mu.201902` UNION ALL
SELECT *, 'yt' AS country_code, 'Mayotte' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_yt.201902` UNION ALL
SELECT *, 'mx' AS country_code, 'Mexico' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_mx.201902` UNION ALL
SELECT *, 'fm' AS country_code, 'Micronesia (Federated States of)' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_fm.201902` UNION ALL
SELECT *, 'md' AS country_code, 'Moldova (Republic of)' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_md.201902` UNION ALL
SELECT *, 'mc' AS country_code, 'Monaco' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_mc.201902` UNION ALL
SELECT *, 'mn' AS country_code, 'Mongolia' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_mn.201902` UNION ALL
SELECT *, 'me' AS country_code, 'Montenegro' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_me.201902` UNION ALL
SELECT *, 'ms' AS country_code, 'Montserrat' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_ms.201902` UNION ALL
SELECT *, 'ma' AS country_code, 'Morocco' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_ma.201902` UNION ALL
SELECT *, 'mz' AS country_code, 'Mozambique' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_mz.201902` UNION ALL
SELECT *, 'mm' AS country_code, 'Myanmar' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_mm.201902` UNION ALL
SELECT *, 'na' AS country_code, 'Namibia' AS country, 'Africa' AS region, 'Southern Africa' AS subregion FROM `chrome-ux-report.country_na.201902` UNION ALL
SELECT *, 'nr' AS country_code, 'Nauru' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_nr.201902` UNION ALL
SELECT *, 'np' AS country_code, 'Nepal' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_np.201902` UNION ALL
SELECT *, 'nl' AS country_code, 'Netherlands' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_nl.201902` UNION ALL
SELECT *, 'nc' AS country_code, 'New Caledonia' AS country, 'Oceania' AS region, 'Melanesia' AS subregion FROM `chrome-ux-report.country_nc.201902` UNION ALL
SELECT *, 'nz' AS country_code, 'New Zealand' AS country, 'Oceania' AS region, 'Australia and New Zealand' AS subregion FROM `chrome-ux-report.country_nz.201902` UNION ALL
SELECT *, 'ni' AS country_code, 'Nicaragua' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_ni.201902` UNION ALL
SELECT *, 'ne' AS country_code, 'Niger' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_ne.201902` UNION ALL
SELECT *, 'ng' AS country_code, 'Nigeria' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_ng.201902` UNION ALL
SELECT *, 'nf' AS country_code, 'Norfolk Island' AS country, 'Oceania' AS region, 'Australia and New Zealand' AS subregion FROM `chrome-ux-report.country_nf.201902` UNION ALL
SELECT *, 'mp' AS country_code, 'Northern Mariana Islands' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_mp.201902` UNION ALL
SELECT *, 'no' AS country_code, 'Norway' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_no.201902` UNION ALL
SELECT *, 'om' AS country_code, 'Oman' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_om.201902` UNION ALL
SELECT *, 'pk' AS country_code, 'Pakistan' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_pk.201902` UNION ALL
SELECT *, 'pw' AS country_code, 'Palau' AS country, 'Oceania' AS region, 'Micronesia' AS subregion FROM `chrome-ux-report.country_pw.201902` UNION ALL
SELECT *, 'ps' AS country_code, 'Palestine, State of' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_ps.201902` UNION ALL
SELECT *, 'pa' AS country_code, 'Panama' AS country, 'Americas' AS region, 'Central America' AS subregion FROM `chrome-ux-report.country_pa.201902` UNION ALL
SELECT *, 'pg' AS country_code, 'Papua New Guinea' AS country, 'Oceania' AS region, 'Melanesia' AS subregion FROM `chrome-ux-report.country_pg.201902` UNION ALL
SELECT *, 'py' AS country_code, 'Paraguay' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_py.201902` UNION ALL
SELECT *, 'pe' AS country_code, 'Peru' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_pe.201902` UNION ALL
SELECT *, 'ph' AS country_code, 'Philippines' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_ph.201902` UNION ALL
SELECT *, 'pl' AS country_code, 'Poland' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_pl.201902` UNION ALL
SELECT *, 'pt' AS country_code, 'Portugal' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_pt.201902` UNION ALL
SELECT *, 'pr' AS country_code, 'Puerto Rico' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_pr.201902` UNION ALL
SELECT *, 'qa' AS country_code, 'Qatar' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_qa.201902` UNION ALL
SELECT *, 're' AS country_code, 'Réunion' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_re.201902` UNION ALL
SELECT *, 'ro' AS country_code, 'Romania' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_ro.201902` UNION ALL
SELECT *, 'ru' AS country_code, 'Russian Federation' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_ru.201902` UNION ALL
SELECT *, 'rw' AS country_code, 'Rwanda' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_rw.201902` UNION ALL
SELECT *, 'bl' AS country_code, 'Saint Barthélemy' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_bl.201902` UNION ALL
SELECT *, 'sh' AS country_code, 'Saint Helena, Ascension and Tristan da Cunha' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_sh.201902` UNION ALL
SELECT *, 'kn' AS country_code, 'Saint Kitts and Nevis' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_kn.201902` UNION ALL
SELECT *, 'lc' AS country_code, 'Saint Lucia' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_lc.201902` UNION ALL
SELECT *, 'mf' AS country_code, 'Saint Martin (French part)' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_mf.201902` UNION ALL
SELECT *, 'pm' AS country_code, 'Saint Pierre and Miquelon' AS country, 'Americas' AS region, 'Northern America' AS subregion FROM `chrome-ux-report.country_pm.201902` UNION ALL
SELECT *, 'vc' AS country_code, 'Saint Vincent and the Grenadines' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_vc.201902` UNION ALL
SELECT *, 'ws' AS country_code, 'Samoa' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_ws.201902` UNION ALL
SELECT *, 'sm' AS country_code, 'San Marino' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_sm.201902` UNION ALL
SELECT *, 'st' AS country_code, 'Sao Tome and Principe' AS country, 'Africa' AS region, 'Middle Africa' AS subregion FROM `chrome-ux-report.country_st.201902` UNION ALL
SELECT *, 'sa' AS country_code, 'Saudi Arabia' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_sa.201902` UNION ALL
SELECT *, 'sn' AS country_code, 'Senegal' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_sn.201902` UNION ALL
SELECT *, 'rs' AS country_code, 'Serbia' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_rs.201902` UNION ALL
SELECT *, 'sc' AS country_code, 'Seychelles' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_sc.201902` UNION ALL
SELECT *, 'sl' AS country_code, 'Sierra Leone' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_sl.201902` UNION ALL
SELECT *, 'sg' AS country_code, 'Singapore' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_sg.201902` UNION ALL
SELECT *, 'sx' AS country_code, 'Sint Maarten (Dutch part)' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_sx.201902` UNION ALL
SELECT *, 'sk' AS country_code, 'Slovakia' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_sk.201902` UNION ALL
SELECT *, 'si' AS country_code, 'Slovenia' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_si.201902` UNION ALL
SELECT *, 'sb' AS country_code, 'Solomon Islands' AS country, 'Oceania' AS region, 'Melanesia' AS subregion FROM `chrome-ux-report.country_sb.201902` UNION ALL
SELECT *, 'so' AS country_code, 'Somalia' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_so.201902` UNION ALL
SELECT *, 'za' AS country_code, 'South Africa' AS country, 'Africa' AS region, 'Southern Africa' AS subregion FROM `chrome-ux-report.country_za.201902` UNION ALL
SELECT *, 'ss' AS country_code, 'South Sudan' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_ss.201902` UNION ALL
SELECT *, 'es' AS country_code, 'Spain' AS country, 'Europe' AS region, 'Southern Europe' AS subregion FROM `chrome-ux-report.country_es.201902` UNION ALL
SELECT *, 'lk' AS country_code, 'Sri Lanka' AS country, 'Asia' AS region, 'Southern Asia' AS subregion FROM `chrome-ux-report.country_lk.201902` UNION ALL
SELECT *, 'sd' AS country_code, 'Sudan' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_sd.201902` UNION ALL
SELECT *, 'sr' AS country_code, 'Suriname' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_sr.201902` UNION ALL
SELECT *, 'sj' AS country_code, 'Svalbard and Jan Mayen' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_sj.201902` UNION ALL
SELECT *, 'sz' AS country_code, 'Swaziland' AS country, 'Africa' AS region, 'Southern Africa' AS subregion FROM `chrome-ux-report.country_sz.201902` UNION ALL
SELECT *, 'se' AS country_code, 'Sweden' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_se.201902` UNION ALL
SELECT *, 'ch' AS country_code, 'Switzerland' AS country, 'Europe' AS region, 'Western Europe' AS subregion FROM `chrome-ux-report.country_ch.201902` UNION ALL
SELECT *, 'sy' AS country_code, 'Syrian Arab Republic' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_sy.201902` UNION ALL
SELECT *, 'tw' AS country_code, 'Taiwan, Province of China' AS country, 'Asia' AS region, 'Eastern Asia' AS subregion FROM `chrome-ux-report.country_tw.201902` UNION ALL
SELECT *, 'tj' AS country_code, 'Tajikistan' AS country, 'Asia' AS region, 'Central Asia' AS subregion FROM `chrome-ux-report.country_tj.201902` UNION ALL
SELECT *, 'tz' AS country_code, 'Tanzania, United Republic of' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_tz.201902` UNION ALL
SELECT *, 'th' AS country_code, 'Thailand' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_th.201902` UNION ALL
SELECT *, 'tl' AS country_code, 'Timor-Leste' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_tl.201902` UNION ALL
SELECT *, 'tg' AS country_code, 'Togo' AS country, 'Africa' AS region, 'Western Africa' AS subregion FROM `chrome-ux-report.country_tg.201902` UNION ALL
SELECT *, 'to' AS country_code, 'Tonga' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_to.201902` UNION ALL
SELECT *, 'tt' AS country_code, 'Trinidad and Tobago' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_tt.201902` UNION ALL
SELECT *, 'tn' AS country_code, 'Tunisia' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_tn.201902` UNION ALL
SELECT *, 'tr' AS country_code, 'Turkey' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_tr.201902` UNION ALL
SELECT *, 'tm' AS country_code, 'Turkmenistan' AS country, 'Asia' AS region, 'Central Asia' AS subregion FROM `chrome-ux-report.country_tm.201902` UNION ALL
SELECT *, 'tc' AS country_code, 'Turks and Caicos Islands' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_tc.201902` UNION ALL
SELECT *, 'tv' AS country_code, 'Tuvalu' AS country, 'Oceania' AS region, 'Polynesia' AS subregion FROM `chrome-ux-report.country_tv.201902` UNION ALL
SELECT *, 'ug' AS country_code, 'Uganda' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_ug.201902` UNION ALL
SELECT *, 'ua' AS country_code, 'Ukraine' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_ua.201902` UNION ALL
SELECT *, 'ae' AS country_code, 'United Arab Emirates' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_ae.201902` UNION ALL
SELECT *, 'gb' AS country_code, 'United Kingdom of Great Britain and Northern Ireland' AS country, 'Europe' AS region, 'Northern Europe' AS subregion FROM `chrome-ux-report.country_gb.201902` UNION ALL
SELECT *, 'us' AS country_code, 'United States of America' AS country, 'Americas' AS region, 'Northern America' AS subregion FROM `chrome-ux-report.country_us.201902` UNION ALL
SELECT *, 'uy' AS country_code, 'Uruguay' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_uy.201902` UNION ALL
SELECT *, 'uz' AS country_code, 'Uzbekistan' AS country, 'Asia' AS region, 'Central Asia' AS subregion FROM `chrome-ux-report.country_uz.201902` UNION ALL
SELECT *, 'vu' AS country_code, 'Vanuatu' AS country, 'Oceania' AS region, 'Melanesia' AS subregion FROM `chrome-ux-report.country_vu.201902` UNION ALL
SELECT *, 've' AS country_code, 'Venezuela (Bolivarian Republic of)' AS country, 'Americas' AS region, 'South America' AS subregion FROM `chrome-ux-report.country_ve.201902` UNION ALL
SELECT *, 'vn' AS country_code, 'Viet Nam' AS country, 'Asia' AS region, 'South-Eastern Asia' AS subregion FROM `chrome-ux-report.country_vn.201902` UNION ALL
SELECT *, 'vg' AS country_code, 'Virgin Islands (British)' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_vg.201902` UNION ALL
SELECT *, 'vi' AS country_code, 'Virgin Islands (U.S.)' AS country, 'Americas' AS region, 'Caribbean' AS subregion FROM `chrome-ux-report.country_vi.201902` UNION ALL
SELECT *, 'eh' AS country_code, 'Western Sahara' AS country, 'Africa' AS region, 'Northern Africa' AS subregion FROM `chrome-ux-report.country_eh.201902` UNION ALL
SELECT *, 'ye' AS country_code, 'Yemen' AS country, 'Asia' AS region, 'Western Asia' AS subregion FROM `chrome-ux-report.country_ye.201902` UNION ALL
SELECT *, 'zm' AS country_code, 'Zambia' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_zm.201902` UNION ALL
SELECT *, 'zw' AS country_code, 'Zimbabwe' AS country, 'Africa' AS region, 'Eastern Africa' AS subregion FROM `chrome-ux-report.country_zw.201902` UNION ALL
SELECT *, 'xk' AS country_code, 'Kosovo' AS country, 'Europe' AS region, 'Eastern Europe' AS subregion FROM `chrome-ux-report.country_xk.201902`)
SELECT
subregion,
SUM(IF(fcp.start < 1000, fcp.density, 0)) / SUM(fcp.density) AS fast_fcp
FROM
countries,
UNNEST(first_contentful_paint.histogram.bin) AS fcp
GROUP BY
subregion
ORDER BY
fast_fcp DESC