SELECT wiki, event.issuesVersion AS version, 
ROUND(100*SUM(IF(event.action = 'issueClicked', 1, 0))/SUM(IF(event.action = 'pageLoaded', 1, 0)),2) AS issues_clickthrough_ratio,
SUM(IF(event.action = 'pageLoaded', 1, 0)) AS pageloaded_events
FROM event.pageissues 
WHERE year = 2018 AND month = 10 AND day =5 AND day <= 25
AND event.sectionnumbers[0] = 0
GROUP BY wiki, event.issuesVersion
ORDER BY wiki, version LIMIT 10000;


wiki	version	issues_clickthrough_ratio	pageloaded_events
enwiki	new2018	0.48	1368370
enwiki	old	0.12	1384509
fawiki	new2018	0.79	232671
fawiki	old	0.23	233577
jawiki	new2018	0.36	2382182
jawiki	old	0.08	2425740
lvwiki	new2018	0.24	2912
lvwiki	old	0.08	2657
ruwiki	new2018	0.53	407005
ruwiki	old	0.07	412372

SELECT wiki, event.issuesVersion AS version, 
ROUND(100*SUM(IF(event.action = 'editClicked', 1, 0))/SUM(IF(event.action = 'pageLoaded', 1, 0)),2) AS edit_clickthrough_ratio,
SUM(IF(event.action = 'pageLoaded', 1, 0)) AS pageloaded_events
FROM event.pageissues 
WHERE year = 2018 AND month = 10 AND day =5 AND day <= 25
AND event.sectionnumbers[0] = 0
GROUP BY wiki, event.issuesVersion
ORDER BY wiki, version LIMIT 10000;


wiki	version	edit_clickthrough_ratio	pageloaded_events
enwiki	new2018	0.37	1368370
enwiki	old	0.39	1384509
fawiki	new2018	0.42	232671
fawiki	old	0.41	233577
jawiki	new2018	0.56	2382182
jawiki	old	0.57	2425740
lvwiki	new2018	0.69	2912
lvwiki	old	0.38	2657
ruwiki	new2018	0.43	407005

SELECT wiki,
(SUM(IF(event.action = 'issueClicked' AND event.issuesVersion = 'new2018', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'new2018', 1, 0)))
/(SUM(IF(event.action = 'issueClicked' AND event.issuesVersion = 'old', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'old', 1, 0)))
AS issues_clickthrough_ratio_change
FROM event.pageissues 
WHERE year = 2018 AND month = 10 AND day =5 AND day <= 25
AND event.sectionnumbers[0] = 0
GROUP BY wiki
ORDER BY wiki LIMIT 10000;


wiki	issues_clickthrough_ratio_change
enwiki	3.8963954121935758
fawiki	3.4464536288048704
jawiki	4.36110353204104
lvwiki	3.1935096153846154
ruwiki	7.761796763854968

SELECT wiki,
(SUM(IF(event.action = 'editClicked' AND event.issuesVersion = 'new2018', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'new2018', 1, 0)))
/(SUM(IF(event.action = 'editClicked' AND event.issuesVersion = 'old', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'old', 1, 0)))
AS edit_clickthrough_ratio_change
FROM event.pageissues 
WHERE year = 2018 AND month = 10 AND day =5 AND day <= 25
AND event.sectionnumbers[0] = 0
GROUP BY wiki
ORDER BY wiki LIMIT 10000;

wiki	edit_clickthrough_ratio_change
enwiki	0.9530882623283146
fawiki	1.0154088560758898
jawiki	0.9794839602389783
lvwiki	1.8248626373626373
ruwiki	0.9942216485694446

SELECT wiki,
(SUM(IF(event.action = 'editClicked' AND event.issuesVersion = 'new2018', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'new2018', 1, 0)))
/(SUM(IF(event.action = 'editClicked' AND event.issuesVersion = 'old', 1, 0))/SUM(IF(event.action = 'pageLoaded' AND event.issuesVersion = 'old', 1, 0)))
AS edit_clickthrough_ratio_change
FROM event.pageissues 
WHERE year = 2018
AND ((month = 10 AND day >=5) OR (month = 11 AND day = 1))
AND event.sectionnumbers[0] = 0
GROUP BY wiki
ORDER BY wiki LIMIT 10000;


wiki	edit_clickthrough_ratio_change
enwiki	0.9854330282194743
fawiki	0.988606839482496
jawiki	0.9908068487665987
lvwiki	1.0736709368487447
ruwiki	0.9700330697087468

SELECT event.issuesseverity[0] AS topseverity,
ROUND(100*SUM(IF(event.action = 'issueClicked', 1, 0))/SUM(IF(event.action = 'pageLoaded', 1, 0)),2) AS issues_clickthrough_ratio_pc,
SUM(IF(event.action = 'pageLoaded', 1, 0)) AS pageloaded_events
FROM event.pageissues 
WHERE year = 2018 AND 
((month = 10 AND day >=5) OR (month = 11 AND day = 1))
AND wiki = 'enwiki'
AND event.sectionnumbers[0] = 0
AND event.issuesVersion = 'new2018'
GROUP BY event.issuesseverity[0]
ORDER BY topseverity LIMIT 10000;

SELECT year, month, day, 
CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date, 
event.issuesseverity[0] AS topseverity,
ROUND(100*SUM(IF(event.action = 'issueClicked', 1, 0))/SUM(IF(event.action = 'pageLoaded', 1, 0)),2) 
AS issues_clickthrough_ratio_pc,
SUM(IF(event.action = 'pageLoaded', 1, 0)) AS pageloaded_events
FROM event.pageissues 
WHERE year = 2018 AND 
((month = 10 AND day >=5) OR (month = 11 AND day = 1))
AND wiki = 'enwiki'
AND event.sectionnumbers[0] = 0
AND event.issuesVersion = 'new2018'
GROUP BY year, month, day, event.issuesseverity[0]
ORDER BY year, month, day, topseverity LIMIT 10000;

SELECT wiki, event.issuesVersion AS version, 
ROUND(100*SUM(IF(event.action = 'modalRedLinkClicked', 1, 0))/SUM(IF(event.action = 'issueClicked', 1, 0)),2) 
  AS red_links,
ROUND(100*SUM(IF(event.action = 'modalInternalClicked', 1, 0))/SUM(IF(event.action = 'issueClicked', 1, 0)),2) 
  AS internal_links,
ROUND(100*SUM(IF(event.action = 'modalEditClicked', 1, 0))/SUM(IF(event.action = 'issueClicked', 1, 0)),2) 
  AS modal_edit_links,
ROUND(100*SUM(IF(event.action = 'modalClose', 1, 0))/SUM(IF(event.action = 'issueClicked', 1, 0)),2) 
  AS modal_closes,
SUM(IF(event.action = 'issueClicked', 1, 0)) AS issue_clicks
FROM event.pageissues 
WHERE year = 2018 AND 
((month = 10 AND day >=5) OR (month = 11 AND day = 1))
GROUP BY wiki, event.issuesVersion
ORDER BY wiki, version LIMIT 10000

SELECT year, month, day,
CONCAT(year,'-',LPAD(month,2,'0'),'-',LPAD(day,2,'0')) AS date, 
wiki, version, 
ROUND(AVG(visiblelength)/1000,2) AS average_visible_length,
ROUND(PERCENTILE(visiblelength, 0.5)/1000,2) AS median_visible_length,
ROUND(EXP(AVG(LOG(visiblelength)))/1000,2) AS expavglog_visible_length,
COUNT(*) AS views
FROM (
  SELECT year, month, day, 
  pi.wiki AS wiki, pi.version AS version, pi.pageToken AS token, 
  visiblelength 
  FROM (
    SELECT year, month, day, 
    wiki, event.issuesVersion AS version,
    event.pageToken AS pageToken
    FROM event.pageissues 
    WHERE year = 2018 
    AND ((month = 10 AND day >=5) OR (month = 11 AND day = 1))
    AND event.sectionnumbers[0] = 0
    AND event.action = 'pageLoaded') AS pi
  JOIN (
    SELECT wiki,
    event.pageToken AS pageToken,
    LEAST(event.visiblelength,3600000) AS visiblelength -- cf. https://meta.wikimedia.org/wiki/Research:Reading_time/Draft_Report#Total_time_spent
    FROM event.readingdepth
    WHERE year = 2018 
    AND ((month = 10 AND day >=5) OR (month = 11 AND day = 1))
    AND event.action = 'pageUnloaded'
    AND ( event.page_issues_a_sample OR event.page_issues_b_sample ) 
    AND ( -- https://meta.wikimedia.org/wiki/Schema_talk:ReadingDepth#Likely_broken_on_Safari_and_some_other_browsers
      (useragent.browser_family != 'Safari') 
      AND (useragent.browser_family != 'Android') 
      AND ((useragent.os_family != 'iOS') OR (CAST(useragent.os_major AS INT) > 11) OR (CAST(useragent.os_minor AS INT) >= 3)) 
      AND ((useragent.browser_family != 'Chrome') OR (CAST(useragent.browser_major AS INT) > 38)) )
    ) AS rd
  ON pi.pageToken = rd.PageToken
  AND visiblelength > 0) AS alltokens
GROUP BY year, month, day, wiki, version
ORDER BY year, month, day, wiki, version LIMIT 100000