Skip to content

Instantly share code, notes, and snippets.

@joakin

joakin/a.sql

Created September 1, 2016 09:14
Show Gist options
  • Save joakin/5941bea3c70b1c914be13e376c18fe28 to your computer and use it in GitHub Desktop.
Save joakin/5941bea3c70b1c914be13e376c18fe28 to your computer and use it in GitHub Desktop.
Download ZIP
hovercards sql stats
Raw
a.sql
SELECT singletonsperday.popupEnabled, singletonsperday.yearmonthday, ROUND(100*singletons/totalevents,2) AS singletons_percentage FROM
(SELECT popupEnabled, yearmonthday, SUM(1) AS singletons FROM
(SELECT event_popupEnabled AS popupEnabled, LEFT(timestamp, 8) AS yearmonthday,
event_sessionToken, event_pageIdSource, event_pageTitleHover,
COUNT(*) AS copies
FROM log.Popups_15777589
WHERE wiki ='huwiki' AND event_isAnon = 1
AND LEFT(timestamp, 8) < '20160828'
AND event_action = 'opened in same tab'
GROUP BY popupEnabled, yearmonthday,
event_sessionToken, event_pageIdSource, event_pageTitleHover
HAVING copies=1) AS singletons
GROUP BY popupEnabled, yearmonthday) AS singletonsperday
JOIN
(SELECT event_popupEnabled AS popupEnabled, LEFT(timestamp, 8) AS yearmonthday,
COUNT(*) AS totalevents
FROM log.Popups_15777589
WHERE wiki ='huwiki' AND event_isAnon = 1
AND LEFT(timestamp, 8) < '20160828'
AND event_action = 'opened in same tab'
GROUP BY popupEnabled, yearmonthday) AS totaleventsperday
ON singletonsperday.popupEnabled = totaleventsperday.popupEnabled
AND singletonsperday.yearmonthday = totaleventsperday.yearmonthday
ORDER BY popupEnabled, yearmonthday;
+--------------+--------------+-----------------------+
| popupEnabled | yearmonthday | singletons_percentage |
+--------------+--------------+-----------------------+
| 0 | 20160728 | 0.17 |
| 0 | 20160729 | 0.11 |
| 0 | 20160730 | 0.34 |
| 0 | 20160731 | 0.09 |
| 0 | 20160801 | 0.18 |
| 0 | 20160802 | 0.14 |
| 0 | 20160803 | 0.23 |
| 0 | 20160804 | 0.20 |
| 0 | 20160805 | 0.12 |
| 0 | 20160806 | 0.19 |
| 0 | 20160807 | 0.17 |
| 0 | 20160808 | 0.18 |
| 0 | 20160809 | 0.17 |
| 0 | 20160810 | 0.20 |
| 0 | 20160811 | 0.22 |
| 0 | 20160812 | 0.23 |
| 0 | 20160813 | 0.26 |
| 0 | 20160814 | 0.25 |
| 0 | 20160815 | 0.19 |
| 0 | 20160816 | 0.14 |
| 0 | 20160817 | 0.12 |
| 0 | 20160818 | 1.73 |
| 0 | 20160819 | 2.43 |
| 0 | 20160820 | 2.39 |
| 0 | 20160821 | 2.43 |
| 0 | 20160822 | 2.36 |
| 0 | 20160823 | 2.36 |
| 0 | 20160824 | 2.31 |
| 0 | 20160825 | 2.25 |
| 0 | 20160826 | 2.36 |
| 0 | 20160827 | 2.65 |
| 1 | 20160728 | 96.73 |
| 1 | 20160729 | 95.72 |
| 1 | 20160730 | 96.11 |
| 1 | 20160731 | 94.19 |
| 1 | 20160801 | 95.36 |
| 1 | 20160802 | 94.93 |
| 1 | 20160803 | 92.72 |
| 1 | 20160804 | 94.86 |
| 1 | 20160805 | 95.08 |
| 1 | 20160806 | 95.11 |
| 1 | 20160807 | 95.82 |
| 1 | 20160808 | 94.75 |
| 1 | 20160809 | 95.04 |
| 1 | 20160810 | 94.70 |
| 1 | 20160811 | 92.95 |
| 1 | 20160812 | 94.72 |
| 1 | 20160813 | 94.52 |
| 1 | 20160814 | 92.30 |
| 1 | 20160815 | 94.36 |
| 1 | 20160816 | 95.30 |
| 1 | 20160817 | 95.77 |
| 1 | 20160818 | 93.99 |
| 1 | 20160819 | 93.31 |
| 1 | 20160820 | 93.66 |
| 1 | 20160821 | 93.42 |
| 1 | 20160822 | 95.72 |
| 1 | 20160823 | 96.06 |
| 1 | 20160824 | 94.24 |
| 1 | 20160825 | 95.93 |
| 1 | 20160826 | 94.49 |
| 1 | 20160827 | 95.52 |
+--------------+--------------+-----------------------+
62 rows in set (1 min 0.18 sec)
Raw
b.sql
# determine ratio of duplicate opens, i.e. same source page and same linked page
# during the same browser session (not necessarily the same page session)
SELECT dupesperday.popupEnabled, dupesperday.yearmonthday, ROUND(100*dupes/totalevents,2) AS dupes_percentage FROM
(SELECT popupEnabled, yearmonthday, SUM(copies-1) AS dupes FROM
(SELECT event_popupEnabled AS popupEnabled, LEFT(timestamp, 8) AS yearmonthday,
event_sessionToken, event_pageIdSource, event_pageTitleHover,
COUNT(*) AS copies
FROM log.Popups_15777589
WHERE wiki ='huwiki' AND event_isAnon = 1
AND LEFT(timestamp, 8) < '20160828'
AND event_action = 'opened in same tab'
GROUP BY popupEnabled, yearmonthday,
event_sessionToken, event_pageIdSource, event_pageTitleHover
HAVING copies>1) AS dupeslist
GROUP BY popupEnabled, yearmonthday) AS dupesperday
JOIN
(SELECT event_popupEnabled AS popupEnabled, LEFT(timestamp, 8) AS yearmonthday,
COUNT(*) AS totalevents
FROM log.Popups_15777589
WHERE wiki ='huwiki' AND event_isAnon = 1
AND LEFT(timestamp, 8) < '20160828'
AND event_action = 'opened in same tab'
GROUP BY popupEnabled, yearmonthday) AS totaleventsperday
ON dupesperday.popupEnabled = totaleventsperday.popupEnabled
AND dupesperday.yearmonthday = totaleventsperday.yearmonthday
ORDER BY popupEnabled, yearmonthday;
+--------------+--------------+------------------+
| popupEnabled | yearmonthday | dupes_percentage |
+--------------+--------------+------------------+
| 0 | 20160728 | 59.93 |
| 0 | 20160729 | 60.72 |
| 0 | 20160730 | 60.42 |
| 0 | 20160731 | 60.81 |
| 0 | 20160801 | 60.39 |
| 0 | 20160802 | 61.50 |
| 0 | 20160803 | 61.99 |
| 0 | 20160804 | 61.18 |
| 0 | 20160805 | 62.33 |
| 0 | 20160806 | 61.96 |
| 0 | 20160807 | 60.69 |
| 0 | 20160808 | 61.37 |
| 0 | 20160809 | 61.37 |
| 0 | 20160810 | 60.77 |
| 0 | 20160811 | 61.17 |
| 0 | 20160812 | 60.47 |
| 0 | 20160813 | 60.95 |
| 0 | 20160814 | 58.82 |
| 0 | 20160815 | 61.13 |
| 0 | 20160816 | 61.07 |
| 0 | 20160817 | 61.17 |
| 0 | 20160818 | 60.34 |
| 0 | 20160819 | 59.96 |
| 0 | 20160820 | 59.65 |
| 0 | 20160821 | 59.68 |
| 0 | 20160822 | 59.56 |
| 0 | 20160823 | 59.42 |
| 0 | 20160824 | 59.63 |
| 0 | 20160825 | 60.21 |
| 0 | 20160826 | 59.11 |
| 0 | 20160827 | 58.64 |
| 1 | 20160728 | 1.79 |
| 1 | 20160729 | 2.21 |
| 1 | 20160730 | 2.08 |
| 1 | 20160731 | 3.03 |
| 1 | 20160801 | 2.49 |
| 1 | 20160802 | 2.79 |
| 1 | 20160803 | 3.70 |
| 1 | 20160804 | 2.70 |
| 1 | 20160805 | 2.63 |
| 1 | 20160806 | 2.54 |
| 1 | 20160807 | 2.19 |
| 1 | 20160808 | 2.85 |
| 1 | 20160809 | 2.58 |
| 1 | 20160810 | 2.83 |
| 1 | 20160811 | 3.91 |
| 1 | 20160812 | 2.77 |
| 1 | 20160813 | 3.08 |
| 1 | 20160814 | 4.21 |
| 1 | 20160815 | 2.97 |
| 1 | 20160816 | 2.49 |
| 1 | 20160817 | 2.21 |
| 1 | 20160818 | 3.14 |
| 1 | 20160819 | 3.51 |
| 1 | 20160820 | 3.46 |
| 1 | 20160821 | 3.57 |
| 1 | 20160822 | 2.19 |
| 1 | 20160823 | 2.05 |
| 1 | 20160824 | 3.07 |
| 1 | 20160825 | 2.14 |
| 1 | 20160826 | 3.03 |
| 1 | 20160827 | 2.58 |
+--------------+--------------+------------------+
62 rows in set (1 min 4.89 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment