Paste P7860

Countries with largest year-over-year pageview changes Q4&Q1 2018
ActivePublic

Authored by Tbayer on Wed, Nov 28, 11:57 PM.
SELECT country_code, changeratio,
ROUND(viewsthisyear/122000000,1) AS milliondailyviewsthisyear
FROM (
SELECT country_code,
ROUND(100*
SUM(IF(year = 2018, view_count, 0))/
SUM(IF(year = 2017, view_count, 0))
-100,1) AS changeratio,
SUM(IF(year = 2018, view_count, 0)) AS viewsthisyear
FROM wmf.projectview_hourly
WHERE
year >= 2017 AND year <= 2018
AND month >= 6 AND month <= 9
AND agent_type = "user"
GROUP BY country_code)
AS countrylist
WHERE viewsthisyear > 122000000
GROUP BY country_code, changeratio, viewsthisyear
ORDER BY ABS(changeratio) DESC LIMIT 50;
country_code changeratio milliondailyviewsthisyear
CN 59.6 5.8
BG 31.5 1.5
IR -23.1 8.7
TW 22.3 8.3
IN 21.8 24.6
-- 21.1 1.3
NL 21.1 8.0
VE -19.0 1.1
UA 17.3 5.5
VN 15.5 2.6
ID 15.5 5.7
TH 11.8 3.2
PK 11.2 4.9
AE 10.6 1.3
MA 10.4 1.3
IE -9.2 2.0
MY 8.4 2.8
HU 8.0 1.9
GR 7.6 2.1
BR 7.4 10.9
IL 7.4 2.8
HR 7.2 1.1
RS 7.1 1.3
SA 6.5 1.8
PL 5.6 8.7
EG 5.6 1.4
SG 5.1 2.3
KR 5.0 3.3
IT 4.9 18.0
CO -4.8 3.7
PH 4.7 4.3
RU -4.6 18.0
SE 4.5 4.7
NO 4.5 2.0
KZ 4.2 1.2
FI 3.8 2.9
FR 3.7 20.1
ES 3.6 9.3
NZ -3.5 1.4
GB 3.3 25.4
PT 3.1 1.5
NG -2.9 1.2
AR 2.5 5.1
US 2.3 112.8
MX -2.1 8.0
CH 1.8 3.3
EC -1.7 1.1
AT 1.6 3.1
DK 1.5 1.8
CA 1.4 12.9
Tbayer created this paste.Wed, Nov 28, 11:57 PM