User:EpochFail/Journal/2011-09-01
Thursday, September 1st, 2011
[edit]I got a request for some queries to be ran so I thought I'd capture my work as I went along. My goal was to find out how much temporary blocking was going on in tlwiki and who was doing it. Then compare with enwiki.
First up is TLWIKI
[edit]How many blocks in TL are temporary?
[edit]SELECT ipb_user, ipb_reason, ipb_expiry
FROM ipblocks WHERE ipb_expiry != "infinity";
--+----------+--------------------------------------------------------------------------------------------------------------------------+----------------+ --| ipb_user | ipb_reason | ipb_expiry | --+----------+--------------------------------------------------------------------------------------------------------------------------+----------------+ --| 0 | Awtomatikong naharang dahil pareho kayo ng IP address ni "Akuindo". Dahilan "Pagpasok ng mga hindi totoong impormasyon". | 20101115023629 | --| 0 | Pagpasok ng mga hindi totoong impormasyon | 20111006231735 | --+----------+--------------------------------------------------------------------------------------------------------------------------+----------------+ --2 rows in set (0.00 sec)
How about infinite blocks?
[edit]SELECT COUNT(*) FROM ipblocks
WHERE ipb_expiry = "infinity";
--+----------+ --| COUNT(*) | --+----------+ --| 135 | --+----------+ --1 row in set (0.00 sec)
Is that all of them?
[edit]SELECT COUNT(*) FROM ipblocks;
--+----------+ --| COUNT(*) | --+----------+ --| 137 | --+----------+ --1 row in set (0.00 sec)
This appears to only be counting up current blocks. I should look for blocks historically instead. I probably want to look in the logging table.
How many blockings took place?
[edit]SELECT count(*) FROM logging
WHERE log_type = "block"
AND log_action = "block";
--+----------+ --| count(*) | --+----------+ --| 552 | --+----------+ --1 row in set (0.02 sec)
Can I understand duration using the logging table?
[edit]SELECT log_params, COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
GROUP BY log_params;
--+----------------------------------------+----------+ --| log_params | count(*) | --+----------------------------------------+----------+ --| 1 day | 12 | --| 1 day | 1 | --| 1 day anononly | 1 | --| 1 day anononly,nocreate | 67 | --| 1 day anononly,nocreate,noautoblock | 6 | --| 1 day nocreate | 10 | --| 1 month | 24 | --| 1 month anononly | 2 | --| 1 month anononly,noautoblock | 1 | --| 1 month anononly,nocreate | 13 | --| 1 month anononly,nocreate,noautoblock | 2 | --| 1 month nocreate | 9 | --| 1 month nocreate,nousertalk | 1 | --| 1 week | 2 | --| 1 week anononly | 2 | --| 1 week anononly,nocreate | 26 | --| 1 week anononly,nocreate,noautoblock | 4 | --| 1 week nocreate | 5 | --| 1 year | 1 | --| 1 year anononly,nocreate | 1 | --| 1 year anononly,nocreate,noautoblock | 2 | --| 1 year nocreate,noemail | 1 | --| 12 hours anononly,nocreate | 1 | --| 2 hours | 1 | --| 2 hours anononly,nocreate | 11 | --| 2 hours anononly,nocreate,noautoblock | 1 | --| 2 hours nocreate | 2 | --| 2 month nocreate | 5 | --| 2 months nocreate | 36 | --| 2 weeks | 1 | --| 2 weeks anononly,nocreate | 7 | --| 2 weeks anononly,nocreate,noautoblock | 2 | --| 2 weeks nocreate | 5 | --| 2008-10-1 anononly,nocreate | 1 | --| 2008-6-20 anononly,nocreate | 1 | --| 2008-6-5 anononly,nocreate | 1 | --| 2008-9-7 anononly,nocreate | 1 | --| 24 hour | 8 | --| 3 days | 7 | --| 3 days anononly | 1 | --| 3 days anononly,nocreate | 32 | --| 3 days anononly,nocreate,noautoblock | 3 | --| 3 days nocreate | 5 | --| 3 months | 1 | --| 3 months anononly,nocreate | 5 | --| 3 months nocreate | 19 | --| 31 hours anononly,nocreate | 1 | --| 4 months nocreate | 90 | --| 5 days nocreate | 8 | --| 6 months anononly,nocreate | 3 | --| 6 months anononly,nocreate,noautoblock | 1 | --| 6 months nocreate | 24 | --| indefinite nocreate,noemail | 1 | --| infinite | 21 | --| infinite anononly,nocreate | 10 | --| infinite anononly,nocreate,autoblock | 1 | --| infinite anononly,nocreate,noautoblock | 3 | --| infinite nocreate | 7 | --| infinite nocreate,noautoblock | 1 | --| infinite nocreate,noemail | 31 | --+----------------------------------------+----------+ --60 rows in set (0.03 sec)
That looks consistent enought. If log_params doesn't contain "infinite" or "indefinite", then it was probably a temporary block.
Who is doing this temporary blocking?
[edit]SELECT
user_id,
user_name,
user_registration,
user_editcount,
COUNT(*) as blockings
FROM logging
INNER JOIN user
ON log_user = user_id
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
GROUP BY user_id, user_name, user_registration, user_editcount
ORDER BY COUNT(*);
--+---------+-------------+-------------------+----------------+-----------+ --| user_id | user_name | user_registration | user_editcount | blockings | --+---------+-------------+-------------------+----------------+-----------+ --| 5863 | Mercy | 20080609082030 | 331 | 1 | --| 6138 | Kylu | 20080627023308 | 21 | 1 | --| 5089 | Dungodung | 20080104110340 | 7 | 1 | --| 5066 | Spacebirdy | 20071222161316 | 35 | 1 | --| 27070 | Matanya | 20110304003045 | 6 | 1 | --| 7414 | Dferg | 20081001073606 | 21 | 1 | --| 4 | Seav | NULL | 198 | 1 | --| 4846 | DerHexer | 20071013110512 | 41 | 2 | --| 7536 | Wutsje | 20081008004641 | 493 | 2 | --| 4096 | Estudyante | 20070729051055 | 2445 | 2 | --| 5007 | Pathoschild | 20071203012931 | 166 | 2 | --| 114 | Sky Harbor | NULL | 2963 | 9 | --| 5112 | Lenticel | 20080109083058 | 2298 | 12 | --| 7341 | Nickrds09 | 20080927014545 | 10463 | 13 | --| 507 | Oscar | 20060116152919 | 6 | 16 | --| 38 | Jojit fb | NULL | 16279 | 26 | --| 4960 | AnakngAraw | 20071118014415 | 113048 | 33 | --| 4512 | Felipe Aira | 20070902123828 | 6482 | 35 | --| 20 | Bluemask | NULL | 23991 | 126 | --| 2509 | Drini | 20070208045607 | 12 | 192 | --+---------+-------------+-------------------+----------------+-----------+ --20 rows in set (1.58 sec)
Math is hard. Just give me the total.
[edit]SELECT COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%";
--+----------+ --| COUNT(*) | --+----------+ --| 477 | --+----------+ --1 row in set (0.00 sec)
OK. I think this is making sense. Let's try limiting things to Aug. 2010 - July 2011
First, the users.
[edit]SELECT
user_id,
user_name,
user_registration,
user_editcount,
COUNT(*) as blockings
FROM logging
INNER JOIN user
ON log_user = user_id
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY user_id, user_name, user_registration, user_editcount
ORDER BY COUNT(*);
--+---------+------------+-------------------+----------------+-----------+ --| user_id | user_name | user_registration | user_editcount | blockings | --+---------+------------+-------------------+----------------+-----------+ --| 27070 | Matanya | 20110304003045 | 6 | 1 | --| 5863 | Mercy | 20080609082030 | 331 | 1 | --| 7536 | Wutsje | 20081008004641 | 493 | 2 | --| 20 | Bluemask | NULL | 23991 | 3 | --| 4960 | AnakngAraw | 20071118014415 | 113048 | 3 | --| 5112 | Lenticel | 20080109083058 | 2298 | 4 | --| 114 | Sky Harbor | NULL | 2963 | 8 | --+---------+------------+-------------------+----------------+-----------+ --7 rows in set (0.02 sec)
Now the total
[edit]SELECT COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000";
--+----------+ --| COUNT(*) | --+----------+ --| 22 | --+----------+ --1 row in set (0.01 sec)
Now for enwiki
[edit]What does infinite blocking look like here?
[edit]SELECT log_params, COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY log_params;
--+--------------------------------------------------------------+----------+ --| log_params | COUNT(*) | --+--------------------------------------------------------------+----------+ --| 0 fortnights nocreate | 1 | --| 0 minutes nocreate | 1 | --| 0 seconds nocreate | 1 | --| 00:01 1 May 2011 noautoblock,nousertalk | 1 | --| 00:30, 1 June 2011 (UTC) anononly,nocreate | 1 | --| 03:44, 26 December 2010 nocreate | 1 | --| 04:12, 13 December 2010 nocreate | 1 | --| 09:41, 14 July 2014 anononly | 1 | --| 1 September 2011 noautoblock,nousertalk | 1 | --| 1 day anononly,nocreate | 25 | --| 1 day nocreate | 6 | --| 1 fortnight anononly,nocreate | 1 | --| 1 hour anononly,nocreate | 10 | --| 1 hour nocreate | 5 | --| 1 minute noautoblock | 1 | --| 1 month | 4 | --| 1 month anononly | 133 | -- -- This keeps going for hundreds of rows. I took the liberty of clipping most -- of them out. Below you'll find all of the ones I either thought were funny -- or should be considered infinite. -- --| 1 year | 5 | --| 1 year anononly | 70 | --| 1 year anononly,nocreate | 3541 | --| 1 year anononly,nocreate,nousertalk | 25 | --| 1 year anononly,nousertalk | 7 | --| 1 year nocreate | 2436 | --| 1 year nocreate,noemail,nousertalk | 2 | --| 1 year nocreate,nousertalk | 11 | --| 1 year nousertalk | 1 | --| 1 year 35 days 3 hours anononly,nocreate | 3 | --| 1 year 35 days 3 hours anononly,nocreate,nousertalk | 1 | --| 1 year 6 months anononly,nocreate | 3 | --| 10000 hours anononly,nocreate | 6 | --| 2 years anononly,nocreate | 1 | --| 2 year anononly,nocreate | 2 | --| 2 years anononly | 2 | --| 2 years anononly,nocreate | 827 | --| 2 years anononly,nocreate,nousertalk | 2 | --| 2 years nocreate | 197 | --| 2 years nocreate,noemail,nousertalk | 1 | --| 2 years nocreate,nousertalk | 3 | --| 200 years nocreate | 1 | --| 3 years anononly,nocreate | 9 | --| 3 Years anononly,nocreate | 2 | --| 3 years anononly,nocreate | 411 | --| 3 years anononly,nocreate,nousertalk | 1 | --| 3 years nocreate | 70 | --| 3 years nocreate,nousertalk | 1 | --| 3 years anononly,nocreate | 1 | --| 35 fortnights anononly,nocreate | 1 | --| 600000 minutes anononly,nocreate | 1 | --| 65000000 years noautoblock | 1 | --| 83 hours 10 minutes 103200 seconds anononly,nocreate | 1 | --| 999999 years nocreate | 1 | --| Sunday anononly,nocreate | 1 | --| indefinite | 117 | --| indefinite anononly | 15 | --| indefinite anononly,nocreate | 120 | --| indefinite anononly,nocreate,nousertalk | 6 | --| indefinite noautoblock | 14284 | --| indefinite noautoblock,noemail | 13 | --| indefinite noautoblock,noemail,nousertalk | 239 | --| indefinite noautoblock,nousertalk | 29 | --| indefinite nocreate | 47616 | --| indefinite nocreate,noautoblock | 1405 | --| indefinite nocreate,noautoblock,noemail | 14 | --| indefinite nocreate,noautoblock,noemail,nousertalk | 3 | --| indefinite nocreate,noautoblock,nousertalk | 12 | --| indefinite nocreate,noemail | 629 | --| indefinite nocreate,noemail,nousertalk | 2683 | --| indefinite nocreate,nousertalk | 663 | --| indefinite noemail | 1 | --| indefinite noemail,nousertalk | 10 | --| indefinite nousertalk | 148 | --| infinite noautoblock | 2 | --| infinite nocreate | 162 | --| infinite nocreate,noautoblock | 1 | --| infinite nocreate,noemail,nousertalk | 29 | --| infinite nocreate,nousertalk | 1 | --+--------------------------------------------------------------+----------+ --481 rows in set (3.47 sec)
It looks like eliminating all of the "indefinite"s and "infinite"s will get almost everything, but there are a few that aren't substantially different. e.g. 65000000 years (haha dinosaurs)
I'm thinking that anything over a year should be considered infinite.
Who is doing the temporary blocking?
[edit]SELECT
user_id,
user_name,
user_registration,
user_editcount,
COUNT(*) as blockings
FROM logging
INNER JOIN user
ON log_user = user_id
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%35 fortnights%"
AND log_params NOT LIKE "%10000 hours%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY user_id, user_name, user_registration, user_editcount
ORDER BY COUNT(*) DESC;
--+----------+-------------------------------------+-------------------+----------------+-----------+ --| user_id | user_name | user_registration | user_editcount | blockings | --+----------+-------------------------------------+-------------------+----------------+-----------+ --| 8760229 | ProcseeBot | 20090119013208 | 0 | 114158 | --| 7852030 | Materialscientist | 20080914014733 | 128044 | 9018 | --| 12225015 | TorNodeBot | 20100425165639 | 10 | 2935 | --| 7007500 | Favonian | 20080430142534 | 100128 | 2824 | --| 491706 | Edgar181 | 20051014160417 | 93056 | 2591 | --| 9336033 | HJ Mitchell | 20090329222346 | 54381 | 1876 | --| 9698266 | Elockid | 20090516231213 | 27497 | 1289 | --| 145394 | Bsadowski1 | 20041203005041 | 15201 | 1285 | --| 365454 | Zzuuzz | 20050803215301 | 83334 | 1280 | --| 1879566 | Bongwarrior | 20060728014701 | 83821 | 1214 | --| 764407 | Kuru | 20060108042514 | 79528 | 1210 | --| 1909773 | JamesBWatson | 20060801164605 | 59451 | 1147 | --| 7167267 | Tide rolls | 20080523125555 | 185383 | 1067 | --| 7219979 | MuZemike | 20080531174706 | 61078 | 1032 | --| 5756587 | Tnxman307 | 20071106200902 | 58228 | 1013 | --| 54381 | Gadfium | 20040325051418 | 86638 | 947 | --| 301395 | NawlinWiki | 20050630034256 | 176401 | 814 | --| 1004750 | Courcelles | 20060228090744 | 186364 | 783 | --| 583020 | A. B. | 20051115190750 | 33192 | 668 | --| 934377 | HelloAnnyong | 20060214224727 | 36030 | 659 | --| 1115773 | JohnCD | 20060320211619 | 69209 | 619 | --| 5389659 | Cirt | 20070922075549 | 142950 | 618 | --| 55327 | Alexf | 20040327135328 | 57518 | 610 | --| 1304678 | Dougweller | 20060423094714 | 67754 | 509 | --| 64875 | Icairns | 20040502200041 | 74370 | 435 | --| 676502 | Nakon | 20051213033622 | 53334 | 397 | --| 4288359 | Toddst1 | 20070502020922 | 64003 | 388 | --| 10728040 | Diannaa | 20091008002650 | 39383 | 373 | --| 84417 | Vsmith | 20040707160723 | 100133 | 361 | --| 4444 | Infrogmation | 20020616225911 | 79879 | 359 | --| 1614547 | Prolog | 20060614092543 | 33480 | 356 | --| 468669 | Davewild | 20051001143512 | 38078 | 322 | --| 1034879 | LessHeard vanU | 20060305232942 | 30478 | 314 | --| 163732 | Daniel Case | 20050106062105 | 85811 | 309 | --| 4727333 | Dabomb87 | 20070626200249 | 65493 | 308 | --| 1257855 | Gogo Dodo | 20060415030914 | 119592 | 307 | --| 44750 | Alison | 20040216203806 | 39926 | 307 | --| 1812441 | PhilKnight | 20060717205003 | 73923 | 301 | --| 8544547 | Shirik | 20081217035148 | 16427 | 288 | --| 5957048 | 7 | 20071206044656 | 32349 | 288 | --| 11386661 | Gfoley4 | 20100108000005 | 29091 | 277 | --| 507787 | Ohnoitsjamie | 20051017194940 | 118028 | 273 | --| 372693 | Closedmouth | 20050905054403 | 129011 | 273 | --| 1865063 | Connormah | 20060726014918 | 31511 | 273 | --| 100426 | Smalljim | 20040903215208 | 27165 | 270 | --| 75230 | Mike Rosoft | 20040611094219 | 41570 | 253 | --| 350890 | Nlu | 20050726005931 | 102338 | 253 | --| 6188256 | Fastily | 20080114000734 | 44382 | 252 | --| 1746167 | Barek | 20060706204133 | 39803 | 247 | --| 5047767 | Rodhullandemu | 20070808165802 | 115150 | 240 | --| 4296922 | Tiptoety | 20070502230226 | 41595 | 221 | --| 3210516 | Kww | 20070109222449 | 54274 | 221 | --| 6413170 | Vianello | 20080211064628 | 14583 | 207 | --| 334792 | Syrthiss | 20050715134317 | 31955 | 200 | --| 55983 | SarekOfVulcan | 20040330064028 | 34967 | 199 | --| 4968133 | GorillaWarfare | 20070728204057 | 16361 | 196 | --| 1224855 | Future Perfect at Sunrise | 20060408232445 | 47816 | 195 | --| 2372780 | J.delanoy | 20061001175701 | 303946 | 192 | --| 712163 | DMacks | 20060116214412 | 53615 | 191 | --| 352579 | Wknight94 | 20050905013846 | 79056 | 189 | --| 1521335 | EdJohnston | 20060529013227 | 25431 | 186 | --| 7573298 | Magog the Ogre | 20080801023612 | 25049 | 184 | --| 1839637 | Acroterion | 20060722012721 | 93548 | 182 | --| 764027 | Soap | 20060108013711 | 19636 | 175 | --| 82432 | Discospinster | 20040627184107 | 140164 | 174 | --| 6774658 | Ronhjones | 20080329222650 | 86144 | 163 | --| 505135 | PeterSymonds | 20051206211053 | 28841 | 160 | --| 488996 | Timotheus Canens | 20051231141340 | 27604 | 160 | --| 690391 | Dreadstar | 20051221083721 | 46430 | 158 | --| 5499713 | Excirial | 20071005144837 | 85522 | 156 | --| 551385 | EncMstr | 20051104002040 | 32271 | 153 | --| 410906 | Paul Erik | NULL | 31662 | 151 | --| 5697725 | NuclearWarfare | 20071030002758 | 75895 | 150 | --| 153741 | Brookie | 20050114170635 | 26418 | 148 | --| 524544 | Amatulic | 20060121020229 | 18782 | 139 | --| 461300 | C.Fred | 20050928034611 | 78797 | 138 | --| 6670376 | Tcncv | 20080315041627 | 17468 | 125 | --| 7044616 | 5 albert square | 20080506001314 | 36779 | 124 | --| 10056298 | Jujutacular | 20090706134834 | 14693 | 123 | --| 831038 | Ckatz | 20060124045055 | 78210 | 122 | --| 6337217 | TFOWR | 20080201071710 | 27123 | 122 | --| 6895866 | Looie496 | 20080415023956 | 14980 | 118 | --| 9792575 | Eagles247 | 20090529191323 | 29057 | 115 | --| 5498730 | Mkativerata | 20071005121629 | 21162 | 113 | --| 451766 | Prodego | 20050923152648 | 28075 | 111 | --| 6362721 | Mifter | 20080204210619 | 16631 | 104 | --| 9661331 | SpacemanSpiff | 20090512023949 | 25442 | 102 | --| 774663 | Scientizzle | 20060110231946 | 26038 | 102 | -- ...Cutting out anyone who did less than 100 blocks... --| 44476 | Capitalistroadster | 20040223120321 | 29148 | 1 | --| 1169106 | Neelix | 20060329211329 | 108484 | 1 | --| 99351 | 23skidoo | 20030104002540 | 37486 | 1 | --+----------+-------------------------------------+-------------------+----------------+-----------+ --660 rows in set (1.61 sec)
How many indefinite blockings?
[edit]SELECT COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%35 fortnights%"
AND log_params NOT LIKE "%10000 hours%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000";
--+----------+ --| COUNT(*) | --+----------+ --| 174199 | --+----------+ --1 row in set (0.96 sec)
And there you have it.
Friday, September 2nd, 2011
[edit]So it looks like I did yesterday's work with the wrong Wiki. I was supposed to work with eswiki rather than tlwiki. Luckily, I figured out all I needed to with the smaller wiki so this should go quickly.
What does infinite blocking look like here?
[edit]SELECT log_params, COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY log_params;
+----------------------------------------------------+----------+ | log_params | COUNT(*) | +----------------------------------------------------+----------+ | | 5 | | 1 day, anononly,nocreate | 8 | | 1 day, anononly,nocreate,nousertalk | 1 | | 1 day, nocreate | 2 | | 1 hour, anononly | 1 | | 1 hour, anononly,nocreate | 1 | | 1 hour, nocreate | 1 | | 1 month, anononly | 4 | | 1 month, anononly,nocreate | 306 | | 1 month, anononly,nocreate,nousertalk | 263 | | 1 month, anononly,nousertalk | 5 | -- Snip snip | 1 year, anononly | 7 | | 1 year, anononly,nocreate | 86 | | 1 year, anononly,nocreate,nousertalk | 58 | | 1 year, anononly,nousertalk | 3 | | 1 year, nocreate | 56 | | 1 year, nocreate,noemail | 3 | | 1 year, nocreate,noemail,nousertalk | 11 | | 1 year, nocreate,nousertalk | 3 | | 10 years, nocreate | 1 | | 2 years, anononly,nocreate | 1 | | 2 years, nocreate | 1 | | 2012-12-03T04:00:00Z, anononly,nocreate,nousertalk | 1 | | 2013-01-01, anononly,nocreate | 1 | | 2013-01-01, anononly,nocreate,nousertalk | 3 | | 2013-01-04T19:50:00Z, anononly | 1 | | 2013-06-01, anononly,nocreate,nousertalk | 2 | | 2014-01-01, anononly,nocreate,nousertalk | 1 | | 2014-03-10, anononly | 1 | | 2015-01-01, anononly,nocreate,nousertalk | 2 | | 3 years, anononly,nocreate | 2 | | 5 years, anononly,nocreate | 2 | | 5 years, nocreate | 1 | | 731 days, anononly,nocreate,nousertalk | 1 | | indefinite, nocreate | 37 | | indefinite, nocreate,noautoblock,noemail,nousertalk | 1 | | indefinite, nocreate,noemail,nousertalk | 53 | | indefinite, nocreate,nousertalk | 1 | | infinite, | 6 | | infinite, anononly | 8 | | infinite, anononly,nocreate | 90 | | infinite, anononly,nocreate,nousertalk | 26 | | infinite, anononly,nousertalk | 22 | | infinite, noautoblock | 6 | | infinite, noautoblock,noemail | 1 | | infinite, noautoblock,noemail,nousertalk | 7 | | infinite, noautoblock,nousertalk | 8 | | infinite, nocreate | 1262 | | infinite, nocreate,noautoblock | 6 | | infinite, nocreate,noautoblock,noemail | 6 | | infinite, nocreate,noautoblock,noemail,nousertalk | 1 | | infinite, nocreate,noemail | 779 | | infinite, nocreate,noemail,nousertalk | 1276 | | infinite, nocreate,nousertalk | 132 | | infinite, noemail,nousertalk | 4 | | infinite, nousertalk | 2 | +-----------------------------------------------------+----------+ 171 rows in set (2.37 sec)
It looks like eliminating all of the "indefinite"s and "infinite"s will get almost everything, but I'll also have to pick out a couple of dates that are in 2013 and 2014
Again, I'm assuming that anything over a year should be considered infinite.
Who is doing the temporary blocking?
[edit]SELECT
user_id,
user_name,
user_registration,
user_editcount,
COUNT(*) as blockings
FROM logging
INNER JOIN user
ON log_user = user_id
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%2012%"
AND log_params NOT LIKE "%2013%"
AND log_params NOT LIKE "%2014%"
AND log_params NOT LIKE "%731 days%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY user_id, user_name, user_registration, user_editcount
ORDER BY COUNT(*) DESC;
+---------+-----------------------+-------------------+----------------+-----------+ | user_id | user_name | user_registration | user_editcount | blockings | +---------+-----------------------+-------------------+----------------+-----------+ | 34991 | Magister Mathematicae | NULL | 43641 | 1712 | | 103601 | -jem- | 20060227110901 | 15953 | 1499 | | 496681 | HUB | 20070904051657 | 67800 | 1187 | | 138808 | Montgomery | 20060503130910 | 32452 | 952 | | 479691 | Tirithel | 20070818015005 | 72411 | 651 | | 879361 | Nixón | 20081106172848 | 27631 | 554 | | 162631 | RoyFocker | 20060610071028 | 50923 | 554 | | 125243 | Antur | 20060408235044 | 39896 | 540 | | 356485 | Racso | 20070403184448 | 13837 | 418 | | 583284 | Poco a poco | 20071209161201 | 97203 | 412 | | 96461 | Laura Fiorucci | 20060211064854 | 31993 | 380 | | 394380 | Obelix83 | 20070509145234 | 49476 | 371 | | 326341 | Lucien leGrey | 20070306150230 | 69283 | 270 | | 342707 | Edmenb | 20070321140431 | 33054 | 265 | | 265898 | PoLuX124 | 20061126021154 | 70247 | 254 | | 32328 | Taichi | NULL | 62638 | 244 | | 242544 | Netito777 | 20061024015837 | 94079 | 244 | | 933637 | Manuelt15 | 20090112080935 | 30349 | 236 | | 43173 | Oscar . | NULL | 35840 | 212 | | 1404757 | Savh | 20100311182908 | 35978 | 204 | | 695527 | Ezarate | 20080430003210 | 48767 | 166 | | 108970 | Durero | 20060310113513 | 26691 | 148 | | 242533 | Humberto | 20061024013918 | 62955 | 142 | | 3883 | Lourdes Cardenal | NULL | 51175 | 136 | | 122377 | Rastrojo | 20060403202924 | 57946 | 128 | | 336388 | 3coma14 | 20070315160502 | 17112 | 117 | | 465375 | Mafores | 20070801020342 | 14935 | 114 | | 176790 | Cratón | 20060708232634 | 11655 | 80 | | 23575 | Ecemaml | NULL | 84726 | 76 | | 598928 | Rαge | 20080109160013 | 43534 | 73 | | 584148 | BetoCG | 20071210224535 | 43020 | 72 | | 24822 | Richy | NULL | 11511 | 71 | | 23834 | Kordas | NULL | 27604 | 66 | | 114709 | Baiji | 20060321025405 | 21369 | 49 | | 7856 | Sanbec | NULL | 30767 | 48 | | 67483 | .José | NULL | 32092 | 48 | | 1122788 | Hprmedina | 20090621042357 | 15085 | 47 | | 2075 | Sabbut | NULL | 63916 | 43 | | 461768 | Aleposta | 20070727032939 | 32427 | 40 | | 39094 | Superzerocool | NULL | 25635 | 37 | | 60082 | Txo | NULL | 25507 | 32 | | 301008 | Raystorm | 20070202184157 | 17555 | 31 | | 291734 | Snakeyes | 20070118221731 | 22961 | 23 | | 76855 | Filipo | NULL | 24356 | 23 | | 23458 | Balderai | NULL | 16911 | 22 | | 183390 | Góngora | 20060723032637 | 17774 | 21 | | 15514 | Cookie | NULL | 34298 | 18 | | 241911 | Millars | 20061023113628 | 36596 | 17 | | 138824 | FrancoGG | 20060503135126 | 8191 | 17 | | 1360 | 4lex | NULL | 8273 | 15 | | 177087 | Isha | 20060709221144 | 34079 | 13 | | 155264 | Mahadeva | 20060529191035 | 6539 | 12 | | 24300 | Desatonao | NULL | 3033 | 10 | | 32684 | Patricio.lorente | NULL | 7762 | 9 | | 280760 | Bucephala | 20061222224319 | 13564 | 8 | | 38723 | Alhen | NULL | 22525 | 7 | | 523639 | Nicop | 20071001203627 | 25481 | 7 | | 536307 | Farisori | 20071013193822 | 63933 | 6 | | 68595 | Morza | NULL | 14369 | 6 | | 370586 | Mushii | 20070417165733 | 13307 | 3 | | 686595 | MarcoAurelio | 20080420111611 | 21035 | 3 | | 1299 | Joseaperez | NULL | 41148 | 3 | | 34231 | Aliman5040 | NULL | 20593 | 3 | | 38328 | Platonides | NULL | 15331 | 2 | | 16584 | Barcex | NULL | 13937 | 2 | | 25063 | FAR | NULL | 26019 | 2 | | 199759 | Kved | 20060822000714 | 25363 | 2 | | 5874 | Angus | NULL | 14923 | 2 | | 141749 | Resped | 20060508142723 | 27448 | 1 | | 410845 | Shanel | 20070525045431 | 1 | 1 | | 93098 | Gizmo II | 20060202033546 | 16476 | 1 | | 15707 | B1mbo | NULL | 37308 | 1 | | 395992 | Cobalttempest | 20070510211453 | 36544 | 1 | +---------+-----------------------+-------------------+----------------+-----------+ 73 rows in set (0.64 sec)
How many indefinite blockings?
[edit]SELECT COUNT(*) FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%35 fortnights%"
AND log_params NOT LIKE "%10000 hours%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000";
+----------+ | COUNT(*) | +----------+ | 13195 | +----------+ 1 row in set (0.19 sec)
Month breakdowns
[edit]Spanish
[edit]SELECT
YEAR(log_timestamp) as year,
MONTH(log_timestamp) as month,
COUNT(*) as blockings,
COUNT(DISTINCT log_user) as blocking_users
FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%2012%"
AND log_params NOT LIKE "%2013%"
AND log_params NOT LIKE "%2014%"
AND log_params NOT LIKE "%731 days%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY YEAR(log_timestamp), MONTH(log_timestamp)
ORDER BY YEAR(log_timestamp), MONTH(log_timestamp);
+------+-------+-----------+----------------+ | year | month | blockings | blocking_users | +------+-------+-----------+----------------+ | 2010 | 8 | 1128 | 45 | | 2010 | 9 | 1390 | 47 | | 2010 | 10 | 1286 | 43 | | 2010 | 11 | 1350 | 43 | | 2010 | 12 | 854 | 38 | | 2011 | 1 | 1246 | 44 | | 2011 | 2 | 1349 | 46 | | 2011 | 3 | 1086 | 41 | | 2011 | 4 | 1128 | 42 | | 2011 | 5 | 1154 | 43 | | 2011 | 6 | 1213 | 40 | +------+-------+-----------+----------------+ 11 rows in set (0.22 sec)
English
[edit]SELECT
YEAR(log_timestamp) as year,
MONTH(log_timestamp) as month,
COUNT(*) as blockings,
COUNT(DISTINCT log_user) as blocking_users
FROM logging
WHERE log_type = "block"
AND log_action = "block"
AND log_params NOT LIKE "%infinite%"
AND log_params NOT LIKE "%indefinite%"
AND log_params NOT LIKE "%year%"
AND log_params NOT LIKE "%2012%"
AND log_params NOT LIKE "%2013%"
AND log_params NOT LIKE "%2014%"
AND log_params NOT LIKE "%731 days%"
AND log_timestamp BETWEEN "20100801000000" AND "20110700000000"
GROUP BY YEAR(log_timestamp), MONTH(log_timestamp)
ORDER BY YEAR(log_timestamp), MONTH(log_timestamp);
+------+-------+-----------+----------------+ | year | month | blockings | blocking_users | +------+-------+-----------+----------------+ | 2010 | 8 | 10393 | 311 | | 2010 | 9 | 14322 | 304 | | 2010 | 10 | 17153 | 297 | | 2010 | 11 | 17592 | 294 | | 2010 | 12 | 11464 | 299 | | 2011 | 1 | 21687 | 312 | | 2011 | 2 | 19524 | 291 | | 2011 | 3 | 20820 | 292 | | 2011 | 4 | 12802 | 273 | | 2011 | 5 | 13757 | 280 | | 2011 | 6 | 14405 | 265 | +------+-------+-----------+----------------+ 11 rows in set (2.23 sec)