RedWarn Users (expanded)

Checks all RedWarn-tagged edits and compiles them into a list of users. Forked from AntiCompositeNumber's "RedWarn users" query.

This expands on the previous script based on suggestions from Ed6767. This helps gauge RedWarn's usage relative to total edits and Twinkle edits. Twinkle is also used here since RedWarn is commonly used in conjunction with Twinkle.

SQL

-- Not good with SQL. This is a complete shot in the dark.
USE enwiki_p;
SELECT 
	actor_name as `Username`,
    user_editcount as `Edit Count`,
    user_registration as `Registration Date`, 
    NOT(ug_group IS NULL)  as `30/500?`, 
    COUNT(CASE WHEN ct_tag_id = 577 THEN 1 END) as `RedWarn Edits`, 
    COUNT(CASE WHEN ct_tag_id = 577 THEN 1 END)/user_editcount*100 as `RedWarn %`, 
    COUNT(CASE WHEN ct_tag_id = 583 THEN 1 END) as `Twinkle Edits`, 
    COUNT(CASE WHEN ct_tag_id = 583 THEN 1 END)/user_editcount*100 as `Twinkle %`, 
    ipb_sitewide as `Currently Blocked`
FROM revision_userindex
JOIN change_tag ON ct_rev_id = rev_id
JOIN actor_revision ON rev_actor = actor_id
JOIN `user` ON actor_user = user_id
LEFT JOIN user_groups ON ug_user = user_id AND ug_group = "extendedconfirmed"
LEFT JOIN ipblocks ON user_id = ipb_user
WHERE ct_tag_id = 577 OR ct_tag_id = 583 -- RedWarn, Twinkle
GROUP BY actor_name
HAVING `RedWarn Edits` > 0
ORDER BY user_registration DESC

SQL

Checking query status...