Fork of All New Page Reviewer (including null) activity since July 8th 2018 (excluding admins) by Insertcleverphrasehere
This query is marked as a draft This query has been published by MPGuy2824.

SQL

x
 
SELECT t1.reviewer, reviews, `last review` FROM
(
  SELECT logtemp.log_user_text AS `reviewer`,
  COUNT(logtemp.log_page) AS `reviews`
  FROM (
  SELECT actor_name AS log_user_text, log_page
  FROM logging_userindex
  JOIN actor_logging ON actor_id = log_actor
  JOIN user_groups ON ug_user = actor_user
  WHERE log_timestamp BETWEEN 20210701000000 AND NOW()
  AND log_type = 'pagetriage-curation'
  AND log_action = 'reviewed'
  AND ug_group = 'patroller'
  AND log_namespace = 0
  UNION
  SELECT actor_name AS log_user_text, log_page
  FROM logging_userindex
  JOIN actor_logging ON actor_id = log_actor
  JOIN user_groups ON ug_user = actor_user
  WHERE log_timestamp BETWEEN 20220630000000 AND NOW()
  AND log_type = 'patrol'
  AND log_action = 'patrol'
  AND ug_group = 'patroller'
  AND log_namespace = 0
  UNION
  SELECT user_name AS log_user_text, NULL AS log_page
  FROM `user`
  JOIN user_groups ON ug_user = user_id
  WHERE ug_group = 'patroller'
  ) logtemp
  GROUP BY reviewer
) t1
LEFT JOIN
(
  SELECT logtemp2.log_user_text AS `reviewer`,
  MAX(logtemp2.log_timestamp) AS `last review`
  FROM (
  SELECT actor_name AS log_user_text, log_timestamp
  FROM logging_userindex
  JOIN actor_logging ON actor_id = log_actor
  JOIN user_groups ON ug_user = actor_user
  WHERE log_timestamp BETWEEN 20210701000000 AND NOW()
  AND log_type = 'pagetriage-curation'
  AND log_action = 'reviewed'
  AND ug_group = 'patroller'
  AND log_namespace = 0
  UNION
  SELECT actor_name, log_timestamp
  FROM logging_userindex
  JOIN actor_logging ON actor_id = log_actor
  JOIN user_groups ON ug_user = actor_user
  WHERE log_timestamp BETWEEN 20220630000000 AND NOW()
  AND log_type = 'patrol'
  AND log_action = 'patrol'
  AND ug_group = 'patroller'
  AND log_namespace = 0
  ) logtemp2
  GROUP BY reviewer
) t2
ON t1.reviewer = t2.reviewer
ORDER BY reviews DESC;
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.

Checking query status...