RedWarn MAT Users

SQL

-- =======================================================================
-- This query will check Wikipedia for all edits tagged with "RedWarn",
-- and find all users of RedWarn from these changes. This also includes
-- users who have used RedWarn previously but have since uninstalled
-- RedWarn. Since RedWarn is commonly used in conjunction with Twinkle,
-- this also counts all Twinkle edits by the users. This will also check
-- if the user is currently blocked or not.
-- =======================================================================
-- NOTES:
-- 
--  * Twinkle only started using the "twinkle" tag on September 2020.
--  * This includes users who have uninstalled RedWarn.
--  * This does not distinguish selfblocks.
--  * This checks for expanded RedWarn features (given if 30/500 passes.)
-- =======================================================================
-- Improved by RW developer Chlod, forked from AntiCompositeNumber's original
-- RW users script. Both versions released under CC0 (per Quarry terms).

USE enwiki_p; -- Use the English Wikipedia database.

SELECT -- Add the following columns...
	actor_name as `Username`,                  -- Editor username
    user_editcount as `Edit Count`,            -- Editor total edit count
    user_registration as `Registration Date`,  -- Editor registration date
    NOT(ug_group IS NULL) as `30/500?`,        -- Passes 30 days and 500 edits
    COUNT(CASE WHEN ct_tag_id = 577 THEN 1 END) as `RedWarn Edits`,                  -- Total RedWarn-tagged edits
    COUNT(CASE WHEN ct_tag_id = 577 THEN 1 END)/user_editcount * 100 as `RedWarn %`, -- Percentage of RW-tagged edits vs. total edits
    IF(ipb_sitewide = 1, 1, NULL) as `Currently Blocked`                             -- If the user is currently blocked (always 1 if true)
FROM revision_userindex -- ... from all Wikipedia revisions

JOIN change_tag ON ct_rev_id = rev_id        -- Combines revision tags with the query
JOIN actor_revision ON rev_actor = actor_id  -- Grabs the actor information to get the username
JOIN `user` ON actor_user = user_id          -- Grabs the user information
JOIN `comment_revision` ON comment_id = rev_comment_id
LEFT JOIN user_groups ON ug_user = user_id AND ug_group = "extendedconfirmed" -- Sets `ug_group` if the user is XCON.
LEFT JOIN ipblocks ON user_id = ipb_user     -- Adds block information to the list
WHERE ct_tag_id = 577 AND comment_text LIKE '%WP:REDWARN/MAT|MAT%'  -- Only get edits tagged "RedWarn"
GROUP BY actor_name                          -- Squash the table based on the username
HAVING `RedWarn Edits` > 0                   -- Only include users who have made a RedWarn edit before
ORDER BY user_registration DESC              -- Order by registration date (latest to oldest)
SQL

Checking query status...
