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

/* 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 u.user_name NOT RLIKE '.*[Bb][Oo][Tt].*';

SELECT gu.gu_name AS Name
FROM centralauth_p.globaluser gu
# Filter on users registered on Meta if you want a single target wiki
LEFT JOIN centralauth_p.localuser lu
ON lu.lu_global_id = gu.gu_id
AND lu.lu_wiki = "metawiki"
WHERE gu.gu_locked = 0
AND gu.gu_name NOT RLIKE '.*[Bb][Oo][Tt].*'
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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...