User:EpochFail/Journal/2011-09-01
< User:EpochFail | Journal
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)