RedWarn MAT Users

SQL

-- =======================================================================
-- Derived from: https://quarry.wmflabs.org/query/48083
-- 
-- This query will show all RedWarn users who have used the MAT feature of
-- RedWarn. This helps in determining users who have tampered with the
-- RedWarn script in order to bypass permission restrictions.
-- =======================================================================
-- 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 comment_text LIKE '%WP:REDWARN/MAT|MAT%' THEN 1 END) as `MAT Edits`,  -- Total RedWarn-tagged MAT 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                        -- 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...
