All New Page Reviewer (including null) activity 1 July 2021 up to and including 30 June 2022 (excluding admins)

SQL

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 = log_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 log_user_text, log_page
  FROM logging_userindex
  JOIN user_groups ON ug_user = log_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 user_name AS log_user_text, log_timestamp
  FROM logging_userindex
  JOIN actor_logging ON actor_id = log_actor
  JOIN user_groups ON ug_user = log_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 log_user_text, log_timestamp
  FROM logging_userindex
  JOIN user_groups ON ug_user = log_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;
SQL

Checking query status...
