User:EpochFail/Journal/2011-09-14

Wednesday, Sept. 14th

I'm hoping to break down the blocking information I reported in the last entry by temp/perm blocks.

English blocking. Temp vs. Perm

This will get the most recent year's blocks by block type and the month in which the block took place.

SELECT 
	IF(
	log_params LIKE "%infinite%" OR
	log_params LIKE "%indefinite%" OR
	log_params LIKE "%year%" OR
	log_params LIKE "%35 fortnights%" OR
	log_params LIKE "%10000 hours%",
	"perm",
	"temp"
	) AS block_type,
	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_timestamp BETWEEN "20100801000000" AND "20110799999999"
GROUP BY 1,2,3
ORDER BY 1,2,3;

+------------+------+-------+-----------+----------------+
| block_type | YEAR | MONTH | blockings | blocking_users |
+------------+------+-------+-----------+----------------+
| perm       | 2010 |     8 |      5468 |            296 |
| perm       | 2010 |     9 |      8520 |            288 |
| perm       | 2010 |    10 |      7914 |            302 |
| perm       | 2010 |    11 |      7401 |            293 |
| perm       | 2010 |    12 |      6258 |            282 |
| perm       | 2011 |     1 |      7720 |            291 |
| perm       | 2011 |     2 |      6378 |            282 |
| perm       | 2011 |     3 |      7444 |            292 |
| perm       | 2011 |     4 |      6431 |            273 |
| perm       | 2011 |     5 |      6403 |            297 |
| perm       | 2011 |     6 |      6276 |            280 |
| perm       | 2011 |     7 |      5905 |            274 |
| temp       | 2010 |     8 |     10397 |            311 |
| temp       | 2010 |     9 |     14324 |            304 |
| temp       | 2010 |    10 |     17207 |            297 |
| temp       | 2010 |    11 |     17660 |            294 |
| temp       | 2010 |    12 |     11490 |            299 |
| temp       | 2011 |     1 |     21707 |            312 |
| temp       | 2011 |     2 |     19544 |            291 |
| temp       | 2011 |     3 |     20845 |            292 |
| temp       | 2011 |     4 |     12818 |            273 |
| temp       | 2011 |     5 |     13785 |            281 |
| temp       | 2011 |     6 |     14422 |            266 |
| temp       | 2011 |     7 |     15000 |            254 |
+------------+------+-------+-----------+----------------+
24 rows in set (1 min 4.94 sec)

Spanish blocking. Temp vs. Perm

SELECT 
	IF(
	log_params LIKE "%infinite%" OR
	log_params LIKE "%indefinite%" OR
	log_params LIKE "%year%" OR
	log_params LIKE "%2012%" OR
	log_params LIKE "%2013%" OR
	log_params LIKE "%2014%" OR
	log_params LIKE "%731 days%",
	"perm",
	"temp"
	) AS block_type,
	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_timestamp BETWEEN "20100801000000" AND "20110799999999"
GROUP BY 1,2,3
ORDER BY 1,2,3;

+------------+------+-------+-----------+----------------+
| block_type | YEAR | MONTH | blockings | blocking_users |
+------------+------+-------+-----------+----------------+
| perm       | 2010 |     8 |       323 |             32 |
| perm       | 2010 |     9 |       268 |             34 |
| perm       | 2010 |    10 |       308 |             34 |
| perm       | 2010 |    11 |       321 |             31 |
| perm       | 2010 |    12 |       380 |             33 |
| perm       | 2011 |     1 |       397 |             31 |
| perm       | 2011 |     2 |       440 |             32 |
| perm       | 2011 |     3 |       377 |             32 |
| perm       | 2011 |     4 |       434 |             34 |
| perm       | 2011 |     5 |       378 |             33 |
| perm       | 2011 |     6 |       354 |             36 |
| perm       | 2011 |     7 |       300 |             34 |
| temp       | 2010 |     8 |      1128 |             45 |
| temp       | 2010 |     9 |      1390 |             47 |
| temp       | 2010 |    10 |      1286 |             43 |
| temp       | 2010 |    11 |      1350 |             43 |
| temp       | 2010 |    12 |       854 |             38 |
| temp       | 2011 |     1 |      1246 |             44 |
| temp       | 2011 |     2 |      1349 |             46 |
| temp       | 2011 |     3 |      1086 |             41 |
| temp       | 2011 |     4 |      1128 |             42 |
| temp       | 2011 |     5 |      1154 |             43 |
| temp       | 2011 |     6 |      1213 |             40 |
| temp       | 2011 |     7 |       777 |             42 |
+------------+------+-------+-----------+----------------+
24 rows in set (1.14 sec)