USE commonswiki_p;

​

SELECT 

    CASE 

        WHEN ug.ug_group = 'sysop' THEN 'Admin'

        WHEN ug.ug_group = 'image-reviewer' THEN 'License Reviewer'

        ELSE 'Other'

    END AS user_type,

    actor.actor_name AS user,

    COUNT(recentchanges.rc_id) AS actions_count

FROM recentchanges

JOIN actor ON recentchanges.rc_actor = actor.actor_id

JOIN comment ON recentchanges.rc_comment_id = comment.comment_id

INNER JOIN 

    (

        SELECT ug_user, ug_group

        FROM user_groups

        WHERE ug_group IN ('sysop', 'image-reviewer')

    ) AS ug ON actor.actor_user = ug.ug_user

WHERE recentchanges.rc_namespace = 6

AND (

      comment.comment_text LIKE '%icense review%' 

      OR comment.comment_text LIKE '%LR passed%'

      OR comment.comment_text LIKE '%: passed%'

     )

GROUP BY user_type, user

ORDER BY user_type, actions_count DESC

LIMIT 500;