Page MenuHomePhabricator
Paste P7900

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

Authored by Tgr on Dec 10 2018, 2:59 AM.
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;

Event Timeline

Tgr created this paste.Dec 10 2018, 2:59 AM

Result:

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