User:EpochFail/Journal/2011-09-14

Wednesday, Sept. 14th

edit

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

English blocking. Temp vs. Perm

edit

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

edit
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)