Skip to content

Instantly share code, notes, and snippets.

@catrope

catrope/cross-wiki-results.txt

Last active March 23, 2016 19:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save catrope/4b044bf77f82794642a7 to your computer and use it in GitHub Desktop.
Save catrope/4b044bf77f82794642a7 to your computer and use it in GitHub Desktop.
Download ZIP
Cross-wiki notifications data 2016-03-23
Raw
cross-wiki-results.txt
# NOTE: Notifications counts are capped at 100 per user per wiki per type (alerts vs messages).
# This means that a user who has 800 alerts on enwiki will be counted as having 100 notifications,
# but a user who has 400 alerts and 400 messages on enwiki will be counted as having 200 notifications,
# and a user who has 400 alerts on enwiki and 400 alerts on commonswiki will also be counted as having 200 notifications.
# Histogram of number of wikis that users have unread notifications on
SELECT numwikis, COUNT(*) AS numusers FROM (SELECT euw_user, COUNT(*) AS numwikis FROM echo_unread_wikis WHERE euw_alerts>0 OR euw_messages > 0 GROUP BY euw_user) AS t GROUP BY numwikis;
+----------+----------+
| numwikis | numusers |
+----------+----------+
| 1 | 8102883 |
| 2 | 879688 |
| 3 | 241663 |
| 4 | 263863 |
| 5 | 87705 |
| 6 | 12795 |
| 7 | 4355 |
| 8 | 2275 |
| 9 | 1404 |
| 10 | 915 |
| 11 | 622 |
| 12 | 436 |
| 13 | 337 |
| 14 | 239 |
| 15 | 195 |
| 16 | 163 |
| 17 | 131 |
| 18 | 117 |
| 19 | 100 |
| 20 | 71 |
| 21 | 67 |
| 22 | 58 |
| 23 | 42 |
| 24 | 26 |
| 25 | 37 |
| 26 | 28 |
| 27 | 31 |
| 28 | 31 |
| 29 | 23 |
| 30 | 26 |
| 31 | 15 |
| 32 | 23 |
| 33 | 21 |
| 34 | 11 |
| 35 | 11 |
| 36 | 21 |
| 37 | 16 |
| 38 | 15 |
| 39 | 8 |
| 40 | 10 |
| 41 | 10 |
| 42 | 9 |
| 43 | 5 |
| 44 | 5 |
| 45 | 10 |
| 46 | 3 |
| 47 | 3 |
| 48 | 4 |
| 50 | 3 |
| 52 | 4 |
| 53 | 4 |
| 54 | 1 |
| 55 | 4 |
| 56 | 3 |
| 57 | 1 |
| 58 | 1 |
| 59 | 1 |
| 60 | 2 |
| 61 | 1 |
| 62 | 3 |
| 63 | 5 |
| 64 | 2 |
| 65 | 1 |
| 66 | 1 |
| 67 | 1 |
| 70 | 3 |
| 73 | 2 |
| 75 | 1 |
| 81 | 2 |
| 84 | 1 |
| 86 | 2 |
| 87 | 1 |
| 95 | 1 |
| 97 | 1 |
| 104 | 1 |
| 110 | 1 |
| 114 | 1 |
| 135 | 1 |
| 136 | 1 |
| 141 | 1 |
| 148 | 1 |
| 232 | 1 |
| 243 | 1 |
| 257 | 1 |
| 268 | 1 |
| 274 | 1 |
| 289 | 1 |
| 329 | 1 |
| 401 | 1 |
| 508 | 1 |
| 564 | 1 |
| 600 | 1 |
| 605 | 1 |
| 655 | 1 |
| 686 | 1 |
| 691 | 1 |
| 697 | 1 |
| 699 | 1 |
| 770 | 1 |
| 793 | 1 |
+----------+----------+
# Same as above, but only considering alerts
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 | 4576050 |
| 2 | 16593 |
| 3 | 1638 |
| 4 | 538 |
| 5 | 269 |
| 6 | 192 |
| 7 | 96 |
| 8 | 90 |
| 9 | 53 |
| 10 | 42 |
| 11 | 33 |
| 12 | 28 |
| 13 | 22 |
| 14 | 21 |
| 15 | 17 |
| 16 | 18 |
| 17 | 10 |
| 18 | 6 |
| 19 | 10 |
| 20 | 2 |
| 21 | 3 |
| 22 | 6 |
| 23 | 6 |
| 24 | 6 |
| 25 | 3 |
| 26 | 2 |
| 27 | 3 |
| 28 | 1 |
| 29 | 1 |
| 31 | 6 |
| 33 | 1 |
| 34 | 1 |
| 35 | 1 |
| 36 | 1 |
| 37 | 1 |
| 39 | 1 |
| 40 | 1 |
| 41 | 3 |
| 44 | 1 |
| 46 | 1 |
| 48 | 1 |
| 49 | 1 |
| 50 | 1 |
| 51 | 1 |
| 53 | 1 |
| 54 | 1 |
| 59 | 1 |
| 64 | 2 |
| 66 | 1 |
| 70 | 2 |
| 82 | 1 |
| 85 | 1 |
| 101 | 1 |
| 221 | 1 |
| 262 | 1 |
+----------+----------+
# Same as above, but only considering messages
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 | 5590686 |
| 2 | 497998 |
| 3 | 240101 |
| 4 | 268863 |
| 5 | 55583 |
| 6 | 9747 |
| 7 | 3621 |
| 8 | 1926 |
| 9 | 1186 |
| 10 | 769 |
| 11 | 502 |
| 12 | 345 |
| 13 | 270 |
| 14 | 206 |
| 15 | 158 |
| 16 | 123 |
| 17 | 105 |
| 18 | 90 |
| 19 | 74 |
| 20 | 58 |
| 21 | 51 |
| 22 | 44 |
| 23 | 31 |
| 24 | 27 |
| 25 | 20 |
| 26 | 26 |
| 27 | 38 |
| 28 | 12 |
| 29 | 17 |
| 30 | 18 |
| 31 | 14 |
| 32 | 20 |
| 33 | 15 |
| 34 | 8 |
| 35 | 7 |
| 36 | 15 |
| 37 | 10 |
| 38 | 14 |
| 39 | 7 |
| 40 | 5 |
| 41 | 8 |
| 42 | 8 |
| 43 | 3 |
| 44 | 5 |
| 45 | 7 |
| 46 | 5 |
| 47 | 2 |
| 48 | 3 |
| 51 | 1 |
| 52 | 4 |
| 53 | 3 |
| 55 | 1 |
| 56 | 3 |
| 58 | 1 |
| 59 | 2 |
| 60 | 1 |
| 62 | 4 |
| 63 | 2 |
| 65 | 1 |
| 66 | 1 |
| 67 | 1 |
| 68 | 1 |
| 70 | 1 |
| 79 | 1 |
| 80 | 1 |
| 93 | 1 |
| 94 | 1 |
| 97 | 1 |
| 110 | 1 |
| 117 | 2 |
| 135 | 1 |
| 141 | 1 |
| 148 | 1 |
| 229 | 1 |
| 243 | 1 |
| 256 | 1 |
| 268 | 1 |
| 329 | 1 |
| 401 | 1 |
| 501 | 1 |
| 563 | 1 |
| 600 | 1 |
| 605 | 1 |
| 652 | 1 |
| 686 | 1 |
| 691 | 1 |
| 696 | 1 |
| 699 | 1 |
| 766 | 1 |
| 791 | 1 |
+----------+----------+
# Histogram of how many unread notifs users have across all wikis
SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_alerts)+SUM(euw_messages) AS numnotifs FROM echo_unread_wikis WHERE euw_alerts > 0 OR euw_messages > 0 GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 1 | 4942111 |
| 2 | 3722679 |
| 3 | 508122 |
| 4 | 277054 |
| 5 | 91912 |
| 6 | 29221 |
| 7 | 8174 |
| 8 | 4106 |
| 9 | 2675 |
| 10 | 1808 |
| 11 | 1331 |
| 12 | 967 |
| 13 | 768 |
| 14 | 676 |
| 15 | 545 |
| 16 | 509 |
| 17 | 376 |
| 18 | 369 |
| 19 | 320 |
| 20 | 275 |
| 21 | 238 |
| 22 | 204 |
| 23 | 209 |
| 24 | 196 |
| 25 | 162 |
| 26 | 130 |
| 27 | 141 |
| 28 | 122 |
| 29 | 105 |
| 30 | 100 |
| 31 | 129 |
| 32 | 120 |
| 33 | 84 |
| 34 | 75 |
| 35 | 72 |
| 36 | 74 |
| 37 | 73 |
| 38 | 106 |
| 39 | 78 |
| 40 | 105 |
| 41 | 75 |
| 42 | 70 |
| 43 | 67 |
| 44 | 85 |
| 45 | 60 |
| 46 | 38 |
| 47 | 62 |
| 48 | 69 |
| 49 | 46 |
| 50 | 56 |
| 51 | 48 |
| 52 | 43 |
| 53 | 55 |
| 54 | 37 |
| 55 | 59 |
| 56 | 37 |
| 57 | 40 |
| 58 | 40 |
| 59 | 34 |
| 60 | 37 |
| 61 | 24 |
| 62 | 39 |
| 63 | 38 |
| 64 | 28 |
| 65 | 35 |
| 66 | 60 |
| 67 | 28 |
| 68 | 16 |
| 69 | 12 |
| 70 | 14 |
| 71 | 23 |
| 72 | 19 |
| 73 | 25 |
| 74 | 10 |
| 75 | 12 |
| 76 | 9 |
| 77 | 8 |
| 78 | 9 |
| 79 | 9 |
| 80 | 8 |
| 81 | 21 |
| 82 | 15 |
| 83 | 96 |
| 84 | 16 |
| 85 | 36 |
| 86 | 32 |
| 87 | 20 |
| 88 | 20 |
| 89 | 12 |
| 90 | 11 |
| 91 | 15 |
| 92 | 28 |
| 93 | 34 |
| 94 | 16 |
| 95 | 13 |
| 96 | 14 |
| 97 | 18 |
| 98 | 10 |
| 99 | 9 |
| 100 | 1444 |
| 101 | 199 |
| 102 | 81 |
| 103 | 71 |
| 104 | 59 |
| 105 | 43 |
| 106 | 32 |
| 107 | 21 |
| 108 | 9 |
| 109 | 12 |
| 110 | 11 |
| 111 | 9 |
| 112 | 8 |
| 113 | 6 |
| 114 | 4 |
| 115 | 3 |
| 116 | 4 |
| 117 | 8 |
| 118 | 8 |
| 119 | 5 |
| 120 | 1 |
| 121 | 4 |
| 122 | 4 |
| 123 | 3 |
| 124 | 3 |
| 125 | 6 |
| 126 | 3 |
| 127 | 1 |
| 128 | 5 |
| 129 | 6 |
| 130 | 1 |
| 133 | 3 |
| 134 | 2 |
| 135 | 5 |
| 136 | 2 |
| 137 | 2 |
| 138 | 2 |
| 139 | 1 |
| 140 | 2 |
| 141 | 2 |
| 142 | 3 |
| 143 | 1 |
| 144 | 1 |
| 146 | 1 |
| 147 | 2 |
| 149 | 2 |
| 151 | 1 |
| 152 | 1 |
| 156 | 2 |
| 158 | 2 |
| 161 | 2 |
| 162 | 2 |
| 165 | 2 |
| 166 | 2 |
| 168 | 1 |
| 174 | 1 |
| 175 | 1 |
| 182 | 1 |
| 184 | 1 |
| 194 | 1 |
| 195 | 1 |
| 200 | 1 |
| 201 | 1 |
| 203 | 2 |
| 204 | 1 |
| 205 | 1 |
| 206 | 1 |
| 211 | 1 |
| 212 | 1 |
| 213 | 1 |
| 214 | 1 |
| 215 | 1 |
| 222 | 1 |
| 223 | 1 |
| 224 | 1 |
| 226 | 1 |
| 233 | 1 |
| 238 | 1 |
| 239 | 1 |
| 244 | 1 |
| 246 | 1 |
| 248 | 2 |
| 263 | 1 |
| 264 | 1 |
| 268 | 1 |
| 271 | 1 |
| 285 | 1 |
| 300 | 1 |
| 314 | 1 |
| 329 | 1 |
| 338 | 2 |
| 341 | 1 |
| 355 | 1 |
| 402 | 1 |
| 413 | 1 |
| 420 | 1 |
| 500 | 1 |
| 512 | 1 |
| 567 | 1 |
| 601 | 1 |
| 605 | 1 |
| 682 | 1 |
| 687 | 1 |
| 691 | 1 |
| 700 | 1 |
| 718 | 1 |
| 794 | 1 |
| 832 | 1 |
| 879 | 1 |
| 1053 | 1 |
| 1280 | 1 |
| 4865 | 1 |
+-----------+----------+
# Histogram of how many unread alerts users have across all wikis
SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_alerts) AS numnotifs FROM echo_unread_wikis WHERE euw_alerts > 0 GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 1 | 4499223 |
| 2 | 63562 |
| 3 | 15055 |
| 4 | 5642 |
| 5 | 2893 |
| 6 | 1753 |
| 7 | 1180 |
| 8 | 871 |
| 9 | 712 |
| 10 | 481 |
| 11 | 415 |
| 12 | 351 |
| 13 | 310 |
| 14 | 269 |
| 15 | 231 |
| 16 | 186 |
| 17 | 176 |
| 18 | 143 |
| 19 | 157 |
| 20 | 136 |
| 21 | 112 |
| 22 | 101 |
| 23 | 104 |
| 24 | 85 |
| 25 | 73 |
| 26 | 70 |
| 27 | 47 |
| 28 | 66 |
| 29 | 47 |
| 30 | 50 |
| 31 | 63 |
| 32 | 42 |
| 33 | 31 |
| 34 | 37 |
| 35 | 22 |
| 36 | 30 |
| 37 | 29 |
| 38 | 58 |
| 39 | 51 |
| 40 | 36 |
| 41 | 35 |
| 42 | 21 |
| 43 | 24 |
| 44 | 19 |
| 45 | 16 |
| 46 | 21 |
| 47 | 15 |
| 48 | 18 |
| 49 | 14 |
| 50 | 20 |
| 51 | 5 |
| 52 | 14 |
| 53 | 15 |
| 54 | 17 |
| 55 | 12 |
| 56 | 12 |
| 57 | 14 |
| 58 | 10 |
| 59 | 13 |
| 60 | 17 |
| 61 | 7 |
| 62 | 9 |
| 63 | 11 |
| 64 | 6 |
| 65 | 1 |
| 66 | 7 |
| 67 | 7 |
| 68 | 15 |
| 69 | 7 |
| 70 | 5 |
| 71 | 4 |
| 72 | 10 |
| 73 | 9 |
| 74 | 7 |
| 75 | 4 |
| 76 | 3 |
| 77 | 3 |
| 78 | 5 |
| 79 | 3 |
| 80 | 5 |
| 81 | 4 |
| 82 | 6 |
| 83 | 2 |
| 84 | 6 |
| 85 | 7 |
| 86 | 4 |
| 87 | 7 |
| 88 | 2 |
| 89 | 2 |
| 90 | 6 |
| 91 | 2 |
| 92 | 4 |
| 93 | 3 |
| 94 | 5 |
| 96 | 5 |
| 97 | 5 |
| 98 | 1 |
| 99 | 1 |
| 100 | 215 |
| 101 | 32 |
| 102 | 11 |
| 103 | 7 |
| 104 | 7 |
| 105 | 3 |
| 106 | 3 |
| 107 | 5 |
| 108 | 4 |
| 109 | 4 |
| 110 | 2 |
| 111 | 1 |
| 112 | 3 |
| 113 | 1 |
| 114 | 2 |
| 115 | 1 |
| 116 | 2 |
| 117 | 2 |
| 118 | 2 |
| 120 | 2 |
| 121 | 2 |
| 122 | 1 |
| 123 | 3 |
| 124 | 2 |
| 128 | 1 |
| 129 | 2 |
| 131 | 1 |
| 132 | 1 |
| 133 | 1 |
| 135 | 1 |
| 136 | 1 |
| 138 | 4 |
| 139 | 1 |
| 142 | 1 |
| 146 | 1 |
| 149 | 1 |
| 153 | 1 |
| 157 | 1 |
| 161 | 2 |
| 163 | 1 |
| 167 | 1 |
| 173 | 1 |
| 178 | 1 |
| 182 | 1 |
| 186 | 1 |
| 193 | 1 |
| 200 | 3 |
| 201 | 1 |
| 204 | 1 |
| 208 | 1 |
| 212 | 1 |
| 213 | 1 |
| 219 | 1 |
| 223 | 1 |
| 225 | 1 |
| 237 | 1 |
| 243 | 1 |
| 255 | 1 |
| 258 | 1 |
| 280 | 1 |
| 281 | 1 |
| 293 | 1 |
| 305 | 1 |
| 309 | 2 |
| 387 | 1 |
| 400 | 1 |
| 490 | 1 |
| 840 | 1 |
| 1041 | 1 |
| 1072 | 1 |
| 4772 | 1 |
+-----------+----------+
# Histogram of how many unread messages users have across all wikis
SELECT numnotifs, COUNT(*) AS numusers FROM (SELECT euw_user, SUM(euw_messages) AS numnotifs FROM echo_unread_wikis WHERE euw_messages > 0 GROUP BY euw_user) AS t GROUP BY numnotifs;
+-----------+----------+
| numnotifs | numusers |
+-----------+----------+
| 1 | 3291164 |
| 2 | 2788649 |
| 3 | 242187 |
| 4 | 270499 |
| 5 | 55749 |
| 6 | 10007 |
| 7 | 3712 |
| 8 | 2092 |
| 9 | 1300 |
| 10 | 854 |
| 11 | 623 |
| 12 | 398 |
| 13 | 319 |
| 14 | 267 |
| 15 | 206 |
| 16 | 239 |
| 17 | 158 |
| 18 | 163 |
| 19 | 121 |
| 20 | 96 |
| 21 | 86 |
| 22 | 75 |
| 23 | 89 |
| 24 | 62 |
| 25 | 50 |
| 26 | 49 |
| 27 | 70 |
| 28 | 50 |
| 29 | 47 |
| 30 | 46 |
| 31 | 69 |
| 32 | 51 |
| 33 | 32 |
| 34 | 28 |
| 35 | 41 |
| 36 | 37 |
| 37 | 32 |
| 38 | 57 |
| 39 | 42 |
| 40 | 36 |
| 41 | 40 |
| 42 | 46 |
| 43 | 31 |
| 44 | 63 |
| 45 | 30 |
| 46 | 26 |
| 47 | 38 |
| 48 | 43 |
| 49 | 24 |
| 50 | 33 |
| 51 | 27 |
| 52 | 38 |
| 53 | 36 |
| 54 | 24 |
| 55 | 49 |
| 56 | 23 |
| 57 | 24 |
| 58 | 28 |
| 59 | 22 |
| 60 | 17 |
| 61 | 12 |
| 62 | 21 |
| 63 | 30 |
| 64 | 19 |
| 65 | 21 |
| 66 | 62 |
| 67 | 14 |
| 68 | 11 |
| 69 | 4 |
| 70 | 5 |
| 71 | 10 |
| 72 | 8 |
| 73 | 14 |
| 74 | 6 |
| 75 | 4 |
| 76 | 7 |
| 77 | 2 |
| 78 | 3 |
| 79 | 8 |
| 80 | 5 |
| 81 | 13 |
| 82 | 8 |
| 83 | 90 |
| 84 | 9 |
| 85 | 30 |
| 86 | 29 |
| 87 | 11 |
| 88 | 21 |
| 89 | 3 |
| 90 | 7 |
| 91 | 13 |
| 92 | 31 |
| 93 | 26 |
| 94 | 9 |
| 95 | 9 |
| 96 | 13 |
| 97 | 8 |
| 98 | 9 |
| 99 | 8 |
| 100 | 1446 |
| 101 | 107 |
| 102 | 53 |
| 103 | 40 |
| 104 | 31 |
| 105 | 18 |
| 106 | 15 |
| 107 | 9 |
| 108 | 8 |
| 109 | 5 |
| 110 | 4 |
| 111 | 2 |
| 112 | 3 |
| 113 | 2 |
| 114 | 2 |
| 115 | 4 |
| 116 | 4 |
| 117 | 4 |
| 118 | 1 |
| 119 | 1 |
| 120 | 1 |
| 121 | 1 |
| 124 | 1 |
| 134 | 1 |
| 135 | 1 |
| 141 | 1 |
| 148 | 1 |
| 150 | 1 |
| 162 | 1 |
| 165 | 2 |
| 203 | 1 |
| 205 | 1 |
| 208 | 1 |
| 243 | 1 |
| 256 | 1 |
| 265 | 1 |
| 268 | 1 |
| 329 | 1 |
| 402 | 1 |
| 501 | 1 |
| 565 | 1 |
| 601 | 1 |
| 605 | 1 |
| 673 | 1 |
| 687 | 1 |
| 691 | 1 |
| 696 | 1 |
| 699 | 1 |
| 792 | 1 |
| 825 | 1 |
+-----------+----------+
# Most popular wiki combinations
SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_alerts>0 OR euw_messages>0 GROUP BY euw_user) AS t GROUP BY wikis ORDER BY numusers DESC LIMIT 20;
+-----------------------------------------------+----------+
| wikis | numusers |
+-----------------------------------------------+----------+
| metawiki | 1103118 |
| eswiki | 776113 |
| frwiki | 571874 |
| commonswiki | 557752 |
| zhwiki | 510615 |
| ruwiki | 406651 |
| dewiki | 330651 |
| ptwiki | 321398 |
| enwiki | 251380 |
| itwiki | 239472 |
| idwiki | 212460 |
| trwiki | 210476 |
| arwiki | 203506 |
| commonswiki,enwikinews,incubatorwiki,metawiki | 189688 |
| viwiki | 174860 |
| jawiki | 155682 |
| nlwiki | 118682 |
| mediawikiwiki | 98487 |
| plwiki | 98441 |
| fawiki | 90739 |
+-----------------------------------------------+----------+
# Most popular wiki combinations, only considering alerts
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 |
+---------------+----------+
| eswiki | 686462 |
| zhwiki | 417684 |
| ruwiki | 356967 |
| frwiki | 344284 |
| ptwiki | 274600 |
| enwiki | 243485 |
| dewiki | 219455 |
| commonswiki | 186617 |
| arwiki | 176697 |
| trwiki | 160764 |
| itwiki | 144307 |
| idwiki | 123546 |
| jawiki | 109664 |
| mediawikiwiki | 77251 |
| kowiki | 70376 |
| nlwiki | 68168 |
| fawiki | 68043 |
| viwiki | 60513 |
| plwiki | 49117 |
| svwiki | 38873 |
+---------------+----------+
# Most popular wiki combinations, only considering messages
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 |
+-----------------------------------------------+----------+
| metawiki | 1394935 |
| frwiki | 559503 |
| commonswiki | 491600 |
| itwiki | 231061 |
| commonswiki,enwikinews,incubatorwiki,metawiki | 218746 |
| eswiki | 213875 |
| idwiki | 208192 |
| dewiki | 162008 |
| viwiki | 147143 |
| trwiki | 138039 |
| zhwiki | 129133 |
| ruwiki | 120544 |
| ptwiki | 110299 |
| commonswiki,enwikinews,incubatorwiki | 98361 |
| arwiki | 93232 |
| jawiki | 92157 |
| commonswiki,metawiki | 76930 |
| enwiki | 70498 |
| nlwiki | 68544 |
| plwiki | 59287 |
+-----------------------------------------------+----------+
# Most popular wiki combinations, excluding "combinations" of one
SELECT wikis, COUNT(*) AS numusers FROM (SELECT euw_user, GROUP_CONCAT(euw_wiki SEPARATOR ',') AS wikis FROM echo_unread_wikis WHERE euw_alerts>0 OR 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,metawiki | 189689 |
| commonswiki,enwikinews,incubatorwiki | 83439 |
| eswiki,metawiki | 77736 |
| commonswiki,metawiki | 73287 |
| frwiki,metawiki | 48314 |
| metawiki,ptwiki | 40422 |
| metawiki,ruwiki | 38251 |
| enwikinews,incubatorwiki,metawiki | 31944 |
| commonswiki,incubatorwiki,metawiki | 29978 |
| arwiki,metawiki | 27711 |
| dewiki,metawiki | 25766 |
| enwikinews,incubatorwiki | 25329 |
| itwiki,metawiki | 23730 |
| idwiki,metawiki | 23170 |
| metawiki,trwiki | 22623 |
| metawiki,zhwiki | 18484 |
| jawiki,metawiki | 16673 |
| commonswiki,enwiki | 12588 |
| commonswiki,frwiki | 12218 |
| mediawikiwiki,metawiki | 11950 |
+-----------------------------------------------+----------+
# Most popular wiki combinations considering only alerts, excluding "combinations" of one
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,eswiki | 1049 |
| commonswiki,enwiki | 800 |
| jawiki,metawiki | 721 |
| commonswiki,frwiki | 432 |
| commonswiki,dewiki | 410 |
| eswiki,wikidatawiki | 340 |
| commonswiki,ptwiki | 320 |
| commonswiki,ruwiki | 274 |
| ruwiki,ukwiki | 255 |
| commonswiki,metawiki | 229 |
| cawiki,eswiki | 205 |
| mediawikiwiki,metawiki | 194 |
| arwiki,frwiki | 169 |
| enwiki,wikidatawiki | 157 |
| dewiki,enwiki | 152 |
| enwiki,metawiki | 142 |
| eswiki,metawiki | 131 |
| commonswiki,jawiki | 129 |
| arwiki,commonswiki | 120 |
| enwiki,frwiki | 112 |
+------------------------+----------+
# Most popular wiki combinations considering only messages, excluding "combinations" of one
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,metawiki | 218749 |
| commonswiki,enwikinews,incubatorwiki | 98359 |
| commonswiki,metawiki | 76931 |
| frwiki,metawiki | 46847 |
| enwikinews,incubatorwiki,metawiki | 36988 |
| commonswiki,incubatorwiki,metawiki | 32767 |
| enwikinews,incubatorwiki | 27968 |
| arwiki,metawiki | 24819 |
| itwiki,metawiki | 22749 |
| idwiki,metawiki | 21590 |
| commonswiki,enwikinews,metawiki | 13838 |
| metawiki,trwiki | 12873 |
| commonswiki,incubatorwiki | 12404 |
| commonswiki,enwikinews | 10553 |
| hiwiki,metawiki | 10385 |
| enwiki,metawiki | 10176 |
| commonswiki,frwiki | 8844 |
| commonswiki,enwikinews,frwiki,incubatorwiki,metawiki | 7946 |
| kowiki,metawiki | 6981 |
| jawiki,metawiki | 6811 |
+------------------------------------------------------+----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment