Utenti attivi in Wikipedia in italiano e Commons (caricamenti e discussioni)

Nomi utente esistenti sia in Commons sia in Wikipedia in italiano, con un certo numero di contributi in entrambi e almeno un contributo recente in Commons, che abbiano anche caricato un certo numero di immagini.

SQL

SET SESSION group_concat_max_len = 100000000;
USE itwiki_p;
SET @l = ( 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 > 200 );
USE commonswiki_p;
SELECT user_name, user_editcount, COUNT(log_id) AS uploads
FROM user
JOIN logging_userindex
ON user_id = log_user
AND log_type = 'upload'
WHERE user_editcount > 100
AND FIND_IN_SET(TO_BASE64(user_name), @l)
AND user_name NOT RLIKE '.*[Bb]ot.*'
AND user_id IN (
  SELECT rev_user
  FROM revision_userindex
  JOIN page
  ON page_id = rev_page
  AND page_namespace IN (4, 5)
  AND rev_timestamp > '20170101000000'
  GROUP BY rev_user )
GROUP BY user_id
ORDER BY uploads DESC
HAVING uploads > 5000;

SQL

Checking query status...