User:EpochFail/Journal/2011-09-01

From Meta, a Wikimedia project coordination wiki

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)