Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Cross-wiki notifs data, round 2
# Histogram of number of wikis that users have unread notifications on
mysql:research@x1-analytics-slave [wikishared]> SELECT numwikis, COUNT(*) AS numusers FROM (SELECT euw_user, COUNT(*) AS numwikis FROM echo_unread_wikis GROUP BY euw_user) AS t GROUP BY numwikis;
+----------+----------+
| numwikis | numusers |
+----------+----------+
| 1 | 31115 |
| 2 | 1832 |
| 3 | 1094 |
| 4 | 496 |
| 5 | 48 |
| 6 | 4 |
| 7 | 3 |
| 8 | 6 |
| 10 | 2 |
| 12 | 1 |
| 14 | 1 |
| 17 | 1 |
| 19 | 1 |
| 73 | 1 |
+----------+----------+
14 rows in set (0.04 sec)
# Same as above, but only considering alerts
mysql:research@x1-analytics-slave [wikishared]> SELECT numwikis, COUNT(*) AS numusers FROM (SELECT euw_user, COUNT(*) AS numwikis FROM echo_unread_wikis WHERE euw_alerts>0 GROUP BY euw_user) AS t GROUP BY numwikis;
+----------+----------+
| numwikis | numusers |
+----------+----------+
| 1 | 21434 |
| 2 | 125 |
| 3 | 10 |
| 4 | 5 |
| 10 | 1 |
| 17 | 1 |
| 62 | 1 |
+----------+----------+
7 rows in set (0.13 sec)
# Same as above, but only considering messages
mysql:research@x1-analytics-slave [wikishared]> SELECT numwikis, COUNT(*) AS numusers FROM (SELECT euw_user, COUNT(*) AS numwikis FROM echo_unread_wikis WHERE euw_messages>0 GROUP BY euw_user) AS t GROUP BY numwikis;
+----------+----------+
| numwikis | numusers |
+----------+----------+
| 1 | 16460 |
| 2 | 1239 |
| 3 | 1226 |
| 4 | 228 |
| 5 | 20 |
| 6 | 3 |
| 7 | 3 |
| 8 | 6 |
| 10 | 1 |
| 12 | 1 |
| 14 | 1 |
| 19 | 1 |
| 53 | 1 |
+----------+----------+
13 rows in set (0.11 sec)
# Histogram of how many unread notifs users have across all wikis
mysql:research@x1-analytics-slave [wikishared]> SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_alerts)+SUM(euw_messages) AS numnotifs FROM echo_unread_wikis GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 1 | 21519 |
| 2 | 6442 |
| 3 | 2096 |
| 4 | 838 |
| 5 | 334 |
| 6 | 177 |
| 7 | 100 |
| 8 | 94 |
| 9 | 64 |
| 10 | 51 |
| 11 | 46 |
| 12 | 43 |
| 13 | 38 |
| 14 | 36 |
| 15 | 36 |
| 16 | 35 |
| 17 | 26 |
| 18 | 21 |
| 19 | 26 |
| 20 | 25 |
| 21 | 24 |
| 22 | 12 |
| 23 | 19 |
| 24 | 14 |
| 25 | 19 |
| 26 | 16 |
| 27 | 16 |
| 28 | 23 |
| 29 | 11 |
| 30 | 8 |
| 31 | 6 |
| 32 | 10 |
| 33 | 10 |
| 34 | 13 |
| 35 | 30 |
| 36 | 23 |
| 37 | 9 |
| 38 | 9 |
| 39 | 7 |
| 40 | 8 |
| 41 | 8 |
| 42 | 21 |
| 43 | 18 |
| 44 | 52 |
| 45 | 11 |
| 46 | 6 |
| 47 | 21 |
| 48 | 19 |
| 49 | 13 |
| 50 | 19 |
| 51 | 13 |
| 52 | 23 |
| 53 | 8 |
| 54 | 6 |
| 55 | 7 |
| 56 | 2 |
| 57 | 8 |
| 58 | 27 |
| 59 | 5 |
| 60 | 9 |
| 61 | 10 |
| 62 | 9 |
| 63 | 4 |
| 64 | 8 |
| 65 | 5 |
| 66 | 45 |
| 67 | 13 |
| 68 | 2 |
| 69 | 5 |
| 70 | 3 |
| 71 | 5 |
| 72 | 3 |
| 73 | 2 |
| 74 | 2 |
| 75 | 1 |
| 76 | 1 |
| 77 | 4 |
| 78 | 2 |
| 79 | 3 |
| 80 | 1 |
| 81 | 20 |
| 82 | 90 |
| 83 | 7 |
| 84 | 32 |
| 85 | 3 |
| 86 | 6 |
| 88 | 11 |
| 89 | 5 |
| 90 | 6 |
| 91 | 8 |
| 92 | 23 |
| 93 | 30 |
| 94 | 3 |
| 95 | 7 |
| 96 | 7 |
| 97 | 6 |
| 98 | 4 |
| 99 | 9 |
| 100 | 1448 |
| 101 | 101 |
| 102 | 12 |
| 103 | 10 |
| 104 | 2 |
| 105 | 4 |
| 106 | 2 |
| 107 | 3 |
| 108 | 1 |
| 110 | 1 |
| 111 | 1 |
| 118 | 1 |
| 120 | 1 |
| 132 | 1 |
| 134 | 1 |
| 137 | 1 |
| 202 | 3 |
| 206 | 1 |
| 831 | 1 |
| 843 | 1 |
+-----------+----------+
118 rows in set (0.11 sec)
# Histogram of how many unread alerts users have across all wikis
mysql:research@x1-analytics-slave [wikishared]> SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_alerts) AS numnotifs FROM echo_unread_wikis GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 0 | 13021 |
| 1 | 18505 |
| 2 | 1328 |
| 3 | 468 |
| 4 | 199 |
| 5 | 160 |
| 6 | 103 |
| 7 | 76 |
| 8 | 56 |
| 9 | 52 |
| 10 | 36 |
| 11 | 32 |
| 12 | 36 |
| 13 | 29 |
| 14 | 30 |
| 15 | 29 |
| 16 | 25 |
| 17 | 14 |
| 18 | 11 |
| 19 | 14 |
| 20 | 17 |
| 21 | 11 |
| 22 | 8 |
| 23 | 14 |
| 24 | 11 |
| 25 | 7 |
| 26 | 13 |
| 27 | 11 |
| 28 | 6 |
| 29 | 5 |
| 30 | 6 |
| 31 | 8 |
| 32 | 7 |
| 33 | 5 |
| 34 | 5 |
| 35 | 5 |
| 36 | 6 |
| 37 | 6 |
| 38 | 2 |
| 39 | 4 |
| 40 | 4 |
| 41 | 9 |
| 42 | 3 |
| 43 | 2 |
| 44 | 4 |
| 45 | 2 |
| 46 | 2 |
| 47 | 1 |
| 48 | 2 |
| 49 | 4 |
| 50 | 3 |
| 51 | 3 |
| 52 | 4 |
| 53 | 3 |
| 54 | 2 |
| 55 | 5 |
| 56 | 2 |
| 57 | 2 |
| 58 | 2 |
| 59 | 1 |
| 60 | 6 |
| 61 | 4 |
| 62 | 3 |
| 63 | 1 |
| 65 | 1 |
| 66 | 3 |
| 67 | 1 |
| 68 | 2 |
| 69 | 1 |
| 70 | 1 |
| 71 | 1 |
| 72 | 1 |
| 74 | 2 |
| 76 | 1 |
| 77 | 1 |
| 78 | 3 |
| 79 | 1 |
| 80 | 2 |
| 81 | 3 |
| 83 | 1 |
| 84 | 2 |
| 85 | 1 |
| 87 | 1 |
| 88 | 1 |
| 89 | 4 |
| 91 | 2 |
| 92 | 1 |
| 93 | 1 |
| 94 | 1 |
| 96 | 2 |
| 98 | 1 |
| 100 | 117 |
| 101 | 2 |
| 102 | 2 |
| 107 | 1 |
| 108 | 1 |
| 133 | 1 |
| 200 | 3 |
| 709 | 1 |
| 835 | 1 |
+-----------+----------+
100 rows in set (0.15 sec)
# Histogram of how many unread messages users have across all wikis
mysql:research@x1-analytics-slave [wikishared]> SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_messages) AS numnotifs FROM echo_unread_wikis GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 0 | 15435 |
| 1 | 12698 |
| 2 | 2440 |
| 3 | 1346 |
| 4 | 299 |
| 5 | 46 |
| 6 | 27 |
| 7 | 24 |
| 8 | 29 |
| 9 | 6 |
| 10 | 8 |
| 11 | 13 |
| 12 | 10 |
| 13 | 8 |
| 14 | 9 |
| 15 | 9 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 11 |
| 20 | 8 |
| 21 | 10 |
| 22 | 4 |
| 23 | 5 |
| 24 | 5 |
| 25 | 10 |
| 26 | 3 |
| 27 | 2 |
| 28 | 18 |
| 29 | 2 |
| 31 | 1 |
| 32 | 3 |
| 33 | 1 |
| 34 | 10 |
| 35 | 31 |
| 36 | 11 |
| 37 | 6 |
| 38 | 3 |
| 39 | 3 |
| 40 | 3 |
| 41 | 2 |
| 42 | 17 |
| 43 | 13 |
| 44 | 55 |
| 45 | 3 |
| 46 | 6 |
| 47 | 18 |
| 48 | 20 |
| 49 | 8 |
| 50 | 13 |
| 51 | 12 |
| 52 | 20 |
| 53 | 3 |
| 54 | 4 |
| 55 | 1 |
| 57 | 8 |
| 58 | 26 |
| 59 | 2 |
| 60 | 7 |
| 62 | 5 |
| 63 | 4 |
| 64 | 8 |
| 65 | 4 |
| 66 | 52 |
| 67 | 3 |
| 68 | 3 |
| 70 | 1 |
| 71 | 5 |
| 72 | 2 |
| 73 | 1 |
| 74 | 1 |
| 75 | 1 |
| 76 | 1 |
| 77 | 1 |
| 79 | 1 |
| 80 | 1 |
| 81 | 19 |
| 82 | 86 |
| 83 | 7 |
| 84 | 30 |
| 85 | 2 |
| 86 | 5 |
| 88 | 12 |
| 90 | 5 |
| 91 | 12 |
| 92 | 26 |
| 93 | 22 |
| 94 | 3 |
| 95 | 7 |
| 96 | 5 |
| 97 | 4 |
| 98 | 5 |
| 99 | 10 |
| 100 | 1420 |
| 101 | 31 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 1 |
| 106 | 1 |
| 113 | 1 |
| 117 | 1 |
| 122 | 1 |
+-----------+----------+
103 rows in set (0.14 sec)
# Most popular wiki combinations
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis GROUP BY euw_user) AS t GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+--------------------------------------+----------+
| wikis | numusers |
+--------------------------------------+----------+
| enwiki | 10039 |
| mediawikiwiki | 2624 |
| commonswiki | 2218 |
| frwiki | 1717 |
| testwiki | 1595 |
| eswiki | 1206 |
| ptwiki | 1100 |
| dewiki | 861 |
| ruwiki | 860 |
| commonswiki,enwikinews,incubatorwiki | 807 |
| itwiki | 747 |
| test2wiki | 720 |
| arwiki | 676 |
| cawiki | 472 |
| zhwiki | 431 |
| jawiki | 400 |
| fawiki | 357 |
| metawiki | 337 |
| idwiki | 315 |
| commonswiki,enwiki | 306 |
+--------------------------------------+----------+
20 rows in set (0.47 sec)
# Most popular wiki combinations, only considering alerts
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_alerts>0 GROUP BY euw_user) AS t GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+---------------+----------+
| wikis | numusers |
+---------------+----------+
| enwiki | 9510 |
| eswiki | 1223 |
| frwiki | 934 |
| dewiki | 776 |
| ruwiki | 715 |
| test2wiki | 684 |
| testwiki | 634 |
| ptwiki | 544 |
| commonswiki | 474 |
| arwiki | 431 |
| jawiki | 392 |
| itwiki | 378 |
| mediawikiwiki | 373 |
| zhwiki | 372 |
| trwiki | 288 |
| idwiki | 214 |
| fawiki | 197 |
| kowiki | 184 |
| nlwiki | 157 |
| viwiki | 153 |
+---------------+----------+
20 rows in set (0.41 sec)
# Most popular wiki combinations, only considering messages
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_messages>0 GROUP BY euw_user) AS t GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+--------------------------------------+----------+
| wikis | numusers |
+--------------------------------------+----------+
| enwiki | 2729 |
| commonswiki | 2468 |
| mediawikiwiki | 2441 |
| frwiki | 1420 |
| commonswiki,enwikinews,incubatorwiki | 1084 |
| testwiki | 1033 |
| ptwiki | 718 |
| itwiki | 632 |
| arwiki | 573 |
| cawiki | 427 |
| ruwiki | 301 |
| fawiki | 289 |
| idwiki | 271 |
| enwikinews,incubatorwiki | 260 |
| metawiki | 244 |
| dewiki | 198 |
| ukwiki | 163 |
| eswiki | 156 |
| commonswiki,enwiki | 146 |
| commonswiki,enwikinews | 142 |
+--------------------------------------+----------+
20 rows in set (0.36 sec)
# Most popular wiki combinations, excluding "combinations" of one
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis GROUP BY euw_user) AS t WHERE wikis LIKE '%,%' GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+---------------------------------------------+----------+
| wikis | numusers |
+---------------------------------------------+----------+
| commonswiki,enwikinews,incubatorwiki | 807 |
| commonswiki,enwiki | 306 |
| enwikinews,incubatorwiki | 229 |
| commonswiki,enwiki,enwikinews,incubatorwiki | 210 |
| commonswiki,incubatorwiki | 120 |
| commonswiki,enwikinews | 99 |
| commonswiki,frwiki | 62 |
| commonswiki,eswiki | 57 |
| commonswiki,enwikinews,frwiki,incubatorwiki | 42 |
| arwiki,enwiki | 37 |
| mediawikiwiki,testwiki | 33 |
| commonswiki,ruwiki | 33 |
| commonswiki,itwiki | 32 |
| enwiki,hiwiki | 29 |
| commonswiki,ptwiki | 27 |
| commonswiki,enwikinews,eswiki,incubatorwiki | 27 |
| commonswiki,trwiki | 27 |
| commonswiki,enwiki,enwikinews | 27 |
| enwiki,idwiki | 23 |
| commonswiki,dewiki | 22 |
+---------------------------------------------+----------+
20 rows in set (0.30 sec)
# Most popular wiki combinations considering only alerts, excluding "combinations" of one
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_alerts>0 GROUP BY euw_user) AS t WHERE wikis LIKE '%,%' GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+------------------------+----------+
| wikis | numusers |
+------------------------+----------+
| commonswiki,enwiki | 10 |
| enwiki,testwiki | 7 |
| enwiki,eswiki | 6 |
| enwiki,metawiki | 4 |
| enwiki,wikidatawiki | 4 |
| frwiki,wikidatawiki | 3 |
| eswiki,wikidatawiki | 3 |
| commonswiki,elwiki | 3 |
| mediawikiwiki,testwiki | 2 |
| enwiki,ptwiki | 2 |
| commonswiki,dewiki | 2 |
| enwiki,enwiktionary | 2 |
| commonswiki,ptwiki | 2 |
| enwiki,orwiki | 2 |
| enwiki,ruwiki | 2 |
| testwiki,wikidatawiki | 2 |
| arwiki,arwiktionary | 2 |
| enwiki,hiwiki | 2 |
| enwiki,simplewiki | 2 |
| dewiki,enwiki | 2 |
+------------------------+----------+
20 rows in set (0.30 sec)
# Most popular wiki combinations considering only messages, excluding "combinations" of one
mysql:research@x1-analytics-slave [wikishared]> SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_messages>0 GROUP BY euw_user) AS t WHERE wikis LIKE '%,%' GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+---------------------------------------------+----------+
| wikis | numusers |
+---------------------------------------------+----------+
| commonswiki,enwikinews,incubatorwiki | 1084 |
| enwikinews,incubatorwiki | 260 |
| commonswiki,enwiki | 146 |
| commonswiki,enwikinews | 142 |
| commonswiki,incubatorwiki | 140 |
| commonswiki,frwiki | 54 |
| commonswiki,enwiki,enwikinews,incubatorwiki | 52 |
| commonswiki,enwikinews,frwiki,incubatorwiki | 44 |
| mediawikiwiki,testwiki | 32 |
| commonswiki,itwiki | 30 |
| commonswiki,ruwiki | 24 |
| commonswiki,enwikinews,hiwiki,incubatorwiki | 15 |
| arwiki,commonswiki | 14 |
| arwiki,frwiki | 13 |
| test2wiki,testwiki | 12 |
| frwiki,itwiki | 12 |
| enwiki,enwikinews,incubatorwiki | 11 |
| commonswiki,enwikinews,incubatorwiki,itwiki | 11 |
| commonswiki,ptwiki | 10 |
| commonswiki,eswiki | 10 |
+---------------------------------------------+----------+
20 rows in set (0.28 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.