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.