wikipedia_version	avg_visited_yesterday
en.m.wikipedia.org	8859519
ja.m.wikipedia.org	1924954
es.m.wikipedia.org	1542810
de.m.wikipedia.org	1093970
ru.m.wikipedia.org	884864
it.m.wikipedia.org	849191
fr.m.wikipedia.org	808798
zh.m.wikipedia.org	391060
pt.m.wikipedia.org	372229
pl.m.wikipedia.org	254385
ar.m.wikipedia.org	245965
fa.m.wikipedia.org	193206
nl.m.wikipedia.org	176551
sv.m.wikipedia.org	144317
id.m.wikipedia.org	123807
fi.m.wikipedia.org	76474
th.m.wikipedia.org	75938
he.m.wikipedia.org	72021
ko.m.wikipedia.org	60220
vi.m.wikipedia.org	51116
20 rows selected (310.205 seconds)

SELECT CONCAT(project,'.m.wikipedia.org') AS wikipedia_version,
INT(AVG(visited_yesterday)) AS avg_visited_yesterday FROM (
SELECT year, month, day, CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date,
project, 
SUM(IF((unix_timestamp(CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')), 'yyyy-MM-dd') - unix_timestamp(wmf_last_access, 'dd-MMM-yyyy')) = 86400,1,0) * view_count) AS visited_yesterday
FROM tbayer.webrequest_extract_bak
WHERE unix_timestamp(wmf_last_access, 'dd-MMM-yyyy') IS NOT NULL 
AND year = 2017 AND month = 6 AND day >= 3 -- 4x7 days, to account for weekly seasonality
AND access_method = 'mobile web'
AND project_class = 'wikipedia'
GROUP BY year, month, day, project) AS byday
GROUP BY project
ORDER BY avg_visited_yesterday DESC LIMIT 20;

Data source is this (slightly clumsy) query:

SELECT CONCAT(all_avgs.project,'.m.wikipedia.org') AS wikipedia_version, 
all_avgs.avg_visited_yesterday AS all_yesterdays,
ios_avgs.avg_visited_yesterday_ios AS ios_yesterdays,
android_avgs.avg_visited_yesterday_android AS android_yesterdays
FROM

(
SELECT project, os_family,
INT(AVG(visited_yesterday)) AS avg_visited_yesterday_ios FROM (
  SELECT year, month, day, CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date,
  project, 
  SUM(IF((unix_timestamp(CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')), 'yyyy-MM-dd') - unix_timestamp(wmf_last_access, 'dd-MMM-yyyy')) = 86400,1,0) * view_count) AS visited_yesterday,
  user_agent_map['os_family'] AS os_family
  FROM tbayer.webrequest_extract_bak
  WHERE unix_timestamp(wmf_last_access, 'dd-MMM-yyyy') IS NOT NULL 
  AND year = 2017 AND month = 6 AND day >= 3 -- 4x7 days, to account for weekly seasonality
  AND access_method = 'mobile web'
  AND project_class = 'wikipedia'
  GROUP BY year, month, day, project, user_agent_map['os_family'] 
  HAVING user_agent_map['os_family'] = 'iOS') AS iosbyday
GROUP BY project, os_family
) AS ios_avgs

JOIN 

(
SELECT project, os_family,
INT(AVG(visited_yesterday)) AS avg_visited_yesterday_android FROM (
  SELECT year, month, day, CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date,
  project, 
  SUM(IF((unix_timestamp(CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')), 'yyyy-MM-dd') - unix_timestamp(wmf_last_access, 'dd-MMM-yyyy')) = 86400,1,0) * view_count) AS visited_yesterday,
  user_agent_map['os_family'] AS os_family
  FROM tbayer.webrequest_extract_bak
  WHERE unix_timestamp(wmf_last_access, 'dd-MMM-yyyy') IS NOT NULL 
  AND year = 2017 AND month = 6 AND day >= 3 -- 4x7 days, to account for weekly seasonality
  AND access_method = 'mobile web'
  AND project_class = 'wikipedia'
  GROUP BY year, month, day, project, user_agent_map['os_family'] 
  HAVING user_agent_map['os_family'] = 'Android') AS androidbyday
GROUP BY project, os_family
) AS android_avgs
ON ios_avgs.project = android_avgs.project

JOIN 
(
SELECT project,
INT(AVG(visited_yesterday)) AS avg_visited_yesterday FROM (
  SELECT year, month, day, CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date,
  project, 
  SUM(IF((unix_timestamp(CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')), 'yyyy-MM-dd') - unix_timestamp(wmf_last_access, 'dd-MMM-yyyy')) = 86400,1,0) * view_count) AS visited_yesterday
  FROM tbayer.webrequest_extract_bak
  WHERE unix_timestamp(wmf_last_access, 'dd-MMM-yyyy') IS NOT NULL 
  AND year = 2017 AND month = 6 AND day >= 3 -- 4x7 days, to account for weekly seasonality
  AND access_method = 'mobile web'
  AND project_class = 'wikipedia'
  GROUP BY year, month, day, project) AS allbyday
GROUP BY project
) AS all_avgs
ON android_avgs.project = all_avgs.project

ORDER BY all_yesterdays DESC LIMIT 20;