NPP revocations

Users not currently in the patroller group, but who once were; who granted and revoked the group, when, and why.

There's no easy way filter out temporary grants.  Expired ones will show up as a grant without a corresponding revoke; these can be confirmed on-wiki with the log_id param, e.g. https://en.wikipedia.org/w/index.php?title=Special:Log&logid=94466636 for Bsherr.

For [[w:en:Special:Diff/882085665]].

SQL

SELECT user1.user_name AS 'patroller name',
	user2.user_name AS 'rights granter/revoker',
	log_timestamp,
	(CASE
		WHEN log_params LIKE '%oldgroups%"patroller"%newgroups%' THEN 'revoked'
		WHEN log_params LIKE '%oldgroups%newgroups%"patroller"%' THEN 'granted'
		ELSE log_params
	END) AS 'action',
	comment_text, log_id
FROM user_former_groups
JOIN user AS user1
	ON ufg_user = user1.user_id
JOIN logging_logindex
	ON log_title = REPLACE(user1.user_name, ' ', '_')
	AND log_type = 'rights' AND log_action = 'rights'
JOIN user AS user2
	ON log_user = user2.user_id
JOIN comment
	ON log_comment_id = comment_id
WHERE ufg_group = 'patroller'
	-- exclude users who had patroller revoked at one time but are currently patroller again
	AND NOT EXISTS (SELECT 1
					FROM user_groups
					WHERE ug_user = ufg_user
						AND ug_group = ufg_group)
	-- exclude rights grants where user didn't have patroller either before or after
	AND log_params LIKE '%"patroller"%'
	-- exclude rights grants where user had patroller both before and after
	AND log_params NOT LIKE '%oldgroups%"patroller"%newgroups%"patroller"%'
ORDER BY user1.user_name, log_timestamp;

SQL

Checking query status...