CrUX ranking in bigquery

_ CrUX ranking data from BigQuery _

https://console.cloud.google.com/bigquery?sq=1095768279152:3d771c7d4d0b4ce7aa31a8846a279904
https://github.com/GoogleChrome/CrUX/tree/main/sql

country-code-lookup-table.sql

SELECT DISTINCT country_code, country from `httparchive-sandbox.test.country_summary_august_2022`

crux-country-summary.sql

CREATE OR REPLACE TABLE `httparchive-sandbox.test.country_summary_august_2022` AS
SELECT origin, country_code, `chrome-ux-report`.experimental.GET_COUNTRY(country_code) AS country FROM (
  SELECT
    DISTINCT origin, country_code, 
  FROM
    `chrome-ux-report.materialized.country_summary`
  WHERE
    yyyymm = 202208 AND
    rank = 1000
  ORDER BY
    country_code,
    origin
)
GROUP BY country_code, origin
ORDER BY country;

global-ranking.sql

SELECT
  DISTINCT origin,
FROM
  `chrome-ux-report.all.202208`
WHERE experimental.popularity.rank = 1000;

num-countries-per-origin.sql

SELECT 
DISTINCT origin, COUNT(*) OVER (PARTITION BY origin) AS num_countries
from `httparchive-sandbox.test.country_summary_august_2022`
ORDER BY num_countries desc

sanity-check.sql

SELECT country_code, count(*) from `httparchive-sandbox.test.country_summary_august_2022` GROUP BY country_code ORDER BY count(*);

-- Validate certain country codes are less than 1000 (623 for 'as')
SELECT DISTINCT origin
FROM `chrome-ux-report.country_as.202208`
WHERE experimental.popularity.rank <= 1000;

z-sanity-check.csv

country_code	f0_
cx	2
nf	6
io	19
er	32
sh	32
tv	36
fk	71
ms	110
sj	114
nr	116
pm	183
mh	199
bl	204
pw	258
ki	261
mp	371
ai	378
ck	381
fm	471
to	580
as	623
st	656
ax	722
mc	791
bq	896
li	912
mf	925
vu	939
af	1000
al	1000
dz	1000
ad	1000
ao	1000
ag	1000
ar	1000
am	1000
aw	1000
au	1000
at	1000
az	1000
bs	1000
bh	1000
bd	1000
bb	1000
by	1000
be	1000
bz	1000
bj	1000
bm	1000
bt	1000
bo	1000
ba	1000
bw	1000
br	1000
bn	1000
bg	1000
bf	1000
bi	1000
cv	1000
kh	1000
cm	1000
ca	1000
ky	1000
cf	1000
td	1000
cl	1000
cn	1000
co	1000
km	1000
cg	1000
cd	1000
cr	1000
hr	1000
cu	1000
cw	1000
cy	1000
cz	1000
ci	1000
dk	1000
dj	1000
dm	1000
do	1000
ec	1000
eg	1000
sv	1000
gq	1000
ee	1000
sz	1000
et	1000
fo	1000
fj	1000
fi	1000
fr	1000
gf	1000
pf	1000
ga	1000
gm	1000
ge	1000
de	1000
gh	1000
gi	1000
gr	1000
gl	1000
gd	1000
gp	1000
gu	1000
gt	1000
gg	1000
gn	1000
gw	1000
gy	1000
ht	1000
hn	1000
hk	1000
hu	1000
is	1000
in	1000
id	1000
ir	1000
iq	1000
ie	1000
im	1000
il	1000
it	1000
jm	1000
jp	1000
je	1000
jo	1000
kz	1000
ke	1000
kr	1000
xk	1000
kw	1000
kg	1000
la	1000
lv	1000
lb	1000
ls	1000
lr	1000
ly	1000
lt	1000
lu	1000
mo	1000
mg	1000
mw	1000
my	1000
mv	1000
ml	1000
mt	1000
mq	1000
mr	1000
mu	1000
yt	1000
mx	1000
md	1000
mn	1000
me	1000
ma	1000
mz	1000
mm	1000
na	1000
np	1000
nl	1000
nc	1000
nz	1000
ni	1000
ne	1000
ng	1000
mk	1000
no	1000
om	1000
pk	1000
ps	1000
pa	1000
pg	1000
py	1000
pe	1000
ph	1000
pl	1000
pt	1000
pr	1000
qa	1000
ro	1000
ru	1000
rw	1000
re	1000
kn	1000
lc	1000
vc	1000
ws	1000
sm	1000
sa	1000
sn	1000
rs	1000
sc	1000
sl	1000
sg	1000
sx	1000
sk	1000
si	1000
sb	1000
so	1000
za	1000
ss	1000
es	1000
lk	1000
sd	1000
sr	1000
se	1000
ch	1000
sy	1000
tw	1000
tj	1000
tz	1000
th	1000
tl	1000
tg	1000
tt	1000
tn	1000
tr	1000
tm	1000
tc	1000
ug	1000
ua	1000
ae	1000
gb	1000
us	1000
uy	1000
uz	1000
ve	1000
vn	1000
vg	1000
vi	1000
eh	1000
ye	1000
zm	1000
zw	1000