Last active
March 23, 2016 19:47
-
-
Save catrope/4b044bf77f82794642a7 to your computer and use it in GitHub Desktop.
Cross-wiki notifications data 2016-03-23
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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