Page MenuHomePhabricator
Paste P7899

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

Authored by Tgr on Dec 10 2018, 2:38 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
) x
GROUP BY year
ORDER BY year ASC
LIMIT 100;

Event Timeline

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

Command for nicer result formatting: cat | cut -c7- | sed '3~3d' | columns -c2 --spread=5

Tgr edited the content of this paste. (Show Details)Dec 10 2018, 2:40 AM
Tgr changed the title of this paste from HQL for number of self-blocks on enwiki per year to HQL for number of self-unblocks on enwiki per year.Dec 10 2018, 2:58 AM
Tgr added a comment.Dec 10 2018, 3:21 AM

Results:

2004     12
2005     243
2006     232
2007     256
2008     193
2009     78
2010     55
2011     32
2012     8
2013     7
2014     2
2015     9
2016     7
2017     15
2018     17