SQL
x
/* Configure how many edits you want */
SET @min = 250;
SET @max = 999;
USE itwiki_p;
SET @itwikiuserpage = ( SELECT GROUP_CONCAT(TO_BASE64(user_name)) AS u
FROM user
RIGHT JOIN page -- Check that a local userpage exists as well
ON page_namespace = 2
AND REPLACE(page_title, '_', ' ') = user_name
AND user_editcount > @min/10 );
SELECT user_name, user_editcount
FROM user u
JOIN centralauth_p.globaluser gu
ON u.user_name = gu.gu_name
AND gu.gu_locked = 0
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb_user
FROM ipblocks
WHERE ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug_user
FROM user_groups
WHERE ug_group = "bot"
)
AND LOWER(u.user_name) NOT LIKE '%bot%';
SELECT gu.gu_name AS Name
FROM centralauth_p.globaluser gu
# Filter on users registered on Meta if you want a single target wiki
JOIN centralauth_p.localuser lu
ON lu.lu_global_id = gu.gu_id
AND lu.lu_wiki = "metawiki"
WHERE gu.gu_locked = 0
AND LOWER(gu.gu_name) NOT LIKE '%bot%'
AND gu.gu_name IN ( SELECT u.user_name
FROM itwiktionary_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwiktionary_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwiktionary_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikiquote_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikiquote_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikiquote_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikibooks_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikibooks_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikibooks_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikisource_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikisource_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikisource_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikinews_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikinews_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikinews_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikiversity_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikiversity_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikiversity_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM itwikivoyage_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM itwikivoyage_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM itwikivoyage_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM pmswiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM pmswiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM pmswiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM lmowiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM lmowiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM lmowiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM scnwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM scnwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM scnwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM napwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM napwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM napwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM vecwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM vecwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM vecwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM cowiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM cowiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM cowiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM lijwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM lijwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM lijwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM furwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM furwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM furwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM scwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM scwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM scwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM emlwiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM emlwiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM emlwiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM cowiktionary_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM cowiktionary_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM cowiktionary_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR gu.gu_name IN ( SELECT u.user_name
FROM cowiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
AND u.user_id NOT IN (
SELECT ipb.ipb_user
FROM cowiki_p.ipblocks ipb
WHERE ipb.ipb_user > 0
)
AND u.user_id NOT IN (
SELECT ug.ug_user
FROM cowiki_p.user_groups ug
WHERE ug.ug_group = "bot"
) )
OR (
gu.gu_name IN (
SELECT u.user_name
FROM commonswiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
)
AND (
gu.gu_name IN (
SELECT REPLACE(p.page_title, "_", " ") AS user_name
FROM commonswiki_p.categorylinks cl
JOIN commonswiki_p.page p
ON cl.cl_to = 'User_it-N'
AND p.page_id = cl.cl_from )
OR FIND_IN_SET(TO_BASE64(gu.gu_name ), @itwikiuserpage )
)
)
OR (
gu.gu_name IN (
SELECT u.user_name
FROM metawiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
)
AND (
gu.gu_name IN (
SELECT REPLACE(p.page_title, "_", " ") AS user_name
FROM metawiki_p.categorylinks cl
JOIN metawiki_p.page p
ON cl.cl_to = 'User_it-N'
AND p.page_id = cl.cl_from )
OR FIND_IN_SET(TO_BASE64(gu.gu_name ), @itwikiuserpage )
)
)
OR (
gu.gu_name IN (
SELECT u.user_name
FROM wikidatawiki_p.user u
WHERE u.user_editcount >= @min
AND u.user_editcount <= @max
)
AND (
gu.gu_name IN (
SELECT REPLACE(p.page_title, "_", " ") AS user_name
FROM wikidatawiki_p.categorylinks cl
JOIN wikidatawiki_p.page p
ON cl.cl_to = 'User_it-N'
AND p.page_id = cl.cl_from )
OR FIND_IN_SET(TO_BASE64(gu.gu_name ), @itwikiuserpage )
)
);
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.