HQL for number of self-unblocks of self-blocks on enwiki per year
ActivePublic
Actions

Authored by Tgr on Dec 10 2018, 2:59 AM.

Tags

None

Referenced Files

	F27464470: HQL for number of self-unblocks of self-blocks on enwiki per year
	Dec 10 2018, 2:59 AM

Subscribers

None

	SELECT
	year,
	count(*) AS cnt
	FROM (
	SELECT
	event_timestamp,
	DATE_FORMAT(event_timestamp, 'yyyy') AS year,
	event_user_text,
	user_text,
	event_comment
	FROM
	mediawiki_history
	WHERE
	snapshot='2018-11'
	AND wiki_db='enwiki'
	AND event_entity='user'
	AND event_type='alterblocks'
	AND event_user_text=user_text
	AND SIZE(user_blocks)=0
	) x
	GROUP BY year
	ORDER BY year ASC
	LIMIT 100;

Result:

2004     1
2005     13
2006     4
2007     15
2008     9
2009     4
2012     1
2018     3

HQL for number of self-unblocks of self-blocks on enwiki per yearActivePublicActions