Patrollers & rollbackers in cswiki

SQL

USE cswiki_p;
SELECT COUNT(*) AS total, SUM(IF(ug_user IS NULL, 0, 1)) AS rollbackers
FROM change_tag
JOIN recentchanges ON rc_id = ct_rc_id
JOIN actor_recentchanges ON actor_id = rc_actor
LEFT JOIN user_groups
	ON ug_user = actor_user AND ug_group = 'rollbacker' AND ug_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'sysop')
WHERE ct_tag_id IN (SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-rollback');

SELECT COUNT(*) AS total, SUM(IF(ug_user IS NULL, 0, 1)) AS patrollers
FROM logging
JOIN actor_logging ON actor_id = log_actor
LEFT JOIN user_groups
	ON ug_user = actor_user AND ug_group = 'patroller' AND ug_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'sysop')
WHERE log_type = 'patrol';
SQL

Checking query status...
