Utenti italofoni con più contributi in tutti i progetti

This query is marked as a draft This query has been published by Nemo bis.

Query to easily make a distribution list for the users having most edits in Wikimedia projects in italic languages, or Italian-language users on multilanguage projects. See also: 
https://quarry.wmflabs.org/query/23956
https://quarry.wmflabs.org/query/38807
https://quarry.wmflabs.org/query/38639

SQL

/* This query is a mix of:
https://quarry.wmflabs.org/query/23956
https://quarry.wmflabs.org/query/38807
https://quarry.wmflabs.org/query/38639

Configure how many edits you want: */
SET @min = 1000;
SET @max = 9999999;
USE itwiki_p;
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 u.user_name NOT RLIKE '.*[Bb]ot.*';
SELECT gu.gu_name AS Name
FROM centralauth_p.globaluser gu
WHERE gu.gu_locked = 0
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
OR gu.gu_name IN ( SELECT u.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
JOIN commonswiki_p.user u
ON REPLACE(p.page_title, "_", " ") = u.user_name
AND u.user_editcount >= @min
AND u.user_editcount <= @max )
OR gu.gu_name IN ( SELECT u.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
JOIN wikidatawiki_p.user u
ON REPLACE(p.page_title, "_", " ") = u.user_name
AND u.user_editcount >= @min
AND u.user_editcount <= @max )
OR gu.gu_name IN ( SELECT u.user_name
FROM metawiki_p.categorylinks cl
JOIN metawiki_p.page p
ON cl.cl_to = 'User_it'
AND cl.cl_sortkey_prefix = 'N'
AND p.page_id = cl.cl_from
JOIN metawiki_p.user u
ON REPLACE(p.page_title, "_", " ") = u.user_name
AND u.user_editcount >= @min
AND u.user_editcount <= @max )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' )
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"
)
AND u.user_name NOT RLIKE '.*[Bb]ot.*' ) );

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...