Recently active license reviewers with lr activity

Fork of Recently active license reviewers with lr activity by Steinsplitter

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

SQL

USE commonswiki_p;
SELECT DISTINCT
 actor_name AS user,
 REPLACE(ug_group,'sysop','admin') AS usergroup,
 ug_bot AS bot
FROM recentchanges
INNER JOIN
  (
   SELECT
   ug_user,
   ug_group
   FROM user_groups
   WHERE ug_group = "sysop"
   OR ug_group = "Image-reviewer"
   ) AS lrgroup /* is the user a lr or sysop? */
ON rc_user = ug_user
LEFT JOIN
  (
   SELECT
   ug_user AS  ug_bot_user,
   ug_group AS ug_bot
   FROM user_groups
   WHERE ug_group = "bot"
   ) AS isbot /* is the user a bot? */
ON rc_user = ug_bot_user
WHERE rc_namespace = "6" /* only file namespace */ 
AND (ug_group="sysop" OR ug_group="Image-reviewer")
AND (
      rc_comment LIKE "%icense review%" /* used by lr gadget*/
      OR rc_comment LIKE "%LR passed%"
      OR rc_comment LIKE "%: passed%" /* for lr bots */
     )
ORDER BY actor_name ASC;

SQL

Checking query status...