Paste P7901

HQL for number of self-unblocks per wiki in a given year
ActivePublic

Authored by Tgr on Dec 10 2018, 3:11 AM.
SELECT
wiki_db,
COUNT(*) cnt
FROM
mediawiki_history
WHERE
snapshot='2018-11'
AND event_entity='user'
AND event_type='alterblocks'
AND event_user_text=user_text
AND date_format(event_timestamp, 'yyyy')=<year>
GROUP BY wiki_db
ORDER BY cnt DESC
LIMIT 1000;
Tgr created this paste.Dec 10 2018, 3:11 AM
Tgr changed the title of this paste from HQL for number of self-unblocks per wiki in 2018 to HQL for number of self-unblocks per wiki in a given year.Dec 10 2018, 3:17 AM
Tgr edited the content of this paste. (Show Details)

2018:

ruwiki		24
zhwiki		18
enwiki		17
frwikiquote	8
frwikivoyage	7
ptwiki		7
enwiktionary	5
nowiki		4
ruwikiquote	4
itwiki		4
viwiki		4
wikidatawiki	3
nlwiki		2
bclwiki		2
etwiki		2
dewikiversity	2
dewiki		2
sqwiki		2
fawiki		1
frwiki		1
hiwiki		1
foundationwiki	1
cswiki		1
ukwiki		1
ruwikinews	1
commonswiki	1
outreachwiki	1
jawiki		1
wawiki		1
Tgr added a comment.Dec 10 2018, 3:18 AM

2017:

enwiki			15
azwiki			13
ptwiki			12
zh_classicalwiki	10
zhwikiquote		8
svwiki			4
cswiki			4
zhwiki			3
hewiki			3
dewikiversity		2
frwiki			2
itwiki			2
commonswiki		2
hewikinews		2
enwiktionary		2
tgwiki			2
ruwiki			2
jawiki			2
viwiki			2
fiwiki			1
itwikiquote		1
huwiki			1
trwikimedia		1
elwiki			1
fawiki			1
afwiki			1
bewiki			1
tgwiktionary		1
Tgr added a comment.Dec 10 2018, 3:19 AM

2016:

hewikibooks	11
zhwiki		9
ptwiki		8
itwiki		8
enwiki		7
ruwiki		6
enwiktionary	4
dewiki		4
cawikiquote	4
wuuwiki		4
viwikisource	4
viwiki		3
hewiki		3
nlwiki		3
wikidatawiki	3
hewikisource	2
frwikiquote	2
frwiki		2
huwiki		2
abwiki		2
trwiki		2
map_bmswiki	2
ukwiki		2
frwiktionary	2
testwiki	2
metawiki	2
commonswiki	1
zhwikinews	1
mediawikiwiki	1
hewikinews	1
srwiki		1
arwiki		1
fiwiki		1
rowiki		1
plwiki		1
ruwiktionary	1
cswiki		1
ltwiki		1
sqwiki		1