New Page Reviewer activity since January 1st 2018 (excluding admins)

Well, this is ugly.  Just adding timestamp as in query 28018 spuriously increases review count by about 10%, since the same review can appear in its first two subqueries with slightly different timestamps; but you can't count distinct log_page either because that would exclude actual multiple reviews of the same page.

SQL

SELECT * FROM
(
SELECT logtemp.log_user_text AS `reviewer`,
COUNT(logtemp.log_page) AS `reviews`
FROM (
SELECT log_user_text, log_page
FROM logging
JOIN user_groups ON ug_user = log_user
WHERE log_timestamp BETWEEN 20180100000000 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
JOIN user_groups ON ug_user = log_user
WHERE log_timestamp BETWEEN 20180100000000 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
JOIN
(
SELECT logtemp2.log_user_text AS `reviewer`,
MAX(logtemp2.log_timestamp) AS `last review`
FROM (
SELECT log_user_text, log_timestamp
FROM logging
JOIN user_groups ON ug_user = log_user
WHERE log_timestamp BETWEEN 20180100000000 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
JOIN user_groups ON ug_user = log_user
WHERE log_timestamp BETWEEN 20180100000000 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...