Users with 200 manual patrols or page curation reviews in 2016

Fork of Users with 200 manual patrols or page curation reviews in 2016 by Cenarium

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

From January 1 to October 6, by users not blocked since January 1.
A page curation review will cause a patrol if the page creation is still in recent changes, and there may be multiple page curation reviews on a single page, hence the count distinct on log_title.

SQL

SELECT CONCAT("[[Special:Contributions/",log_user_text,"|",log_user_text,"]]") AS name, COUNT(DISTINCT log_title) AS num
FROM logging_userindex
WHERE log_timestamp BETWEEN 20160101000000 AND 20161006000000
AND log_deleted = 0
AND log_namespace = 0
AND ( (
    log_type = 'patrol'
    AND log_params NOT LIKE '%::auto";i:1%'
  ) OR (
    log_type = 'pagetriage-curation'
    AND log_action = 'reviewed'
) )
AND (SELECT COUNT(*) FROM ipblocks WHERE ipb_user = log_user AND ipb_timestamp > '20160101000000') = 0
AND (SELECT COUNT(*) FROM user_groups WHERE ug_user = log_user AND (ug_group = 'reviewer' OR ug_group = 'sysop' OR ug_group = 'patroller') = 0
GROUP BY name
HAVING num >= 200
LIMIT 10

SQL

Checking query status...