RedWarn Users (expanded)

This query is marked as a draft This query has been published by Chlod.

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

-- =======================================================================
-- 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).

SELECT -- Add the following columns...
	`actor_name` as `Username`,                  -- Editor username
    `user_editcount` as `Edit Count`,            -- Editor total edit count
    DATE_FORMAT(`user_registration`, "%Y-%m-%d %H:%i:%s") as `Registration Date`,  -- Editor registration date
    IF(NOT(xc.ug_group IS NULL AND so.ug_group IS NULL), "Y", NULL) as `XC?`,         -- Extended confirmed
    IF(NOT(so.ug_group IS NULL), "Y", NULL) as `sysop?`,      -- System operator
    IF(NOT(ia.ug_group IS NULL), "Y", NULL) as `intadmin?`,      -- System operator
    COUNT(IF(ct_tag_id = 577 OR ct_tag_id = 618, 1, NULL)) as `RedWarn Edits`,                  -- Total RedWarn-tagged edits
    COUNT(IF(ct_tag_id = 577 OR ct_tag_id = 618, 1, NULL))/user_editcount * 100 as `RedWarn %`, -- Percentage of RW-tagged edits vs. total edits
    MAX(IF(ct_tag_id = 577 OR ct_tag_id = 618, DATE_FORMAT(`rev_timestamp`, "%Y-%m-%d %H:%i:%s"), NULL)) as `Last RW edit`,       -- Get latest RW edit
    COUNT(IF(ct_tag_id = 583, 1, NULL)) as `Twinkle Edits`,                  -- Total Twinkle-tagged edits
    COUNT(IF(ct_tag_id = 583, 1, NULL))/user_editcount * 100 as `Twinkle %`, -- Percentage of TW-tagged edits vs. total edits
    -- RedWarn to Twinkle edit ratio
    COUNT(IF(ct_tag_id = 577 OR ct_tag_id = 618, 1, NULL))/COUNT(IF(ct_tag_id = 583, 1, NULL)) as `RW ÷ TW`,
    IF(ipb_sitewide = 1, "Y", 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
LEFT JOIN user_groups xc ON xc.ug_user = user_id AND xc.ug_group = "extendedconfirmed" -- Sets `xc.ug_group` if the user is XCON.
LEFT JOIN user_groups so ON so.ug_user = user_id AND so.ug_group = "sysop"             -- Sets `so.ug_group` if the user is a sysop.
LEFT JOIN user_groups ia ON ia.ug_user = user_id AND ia.ug_group = "interface-admin"   -- Sets `so.ug_group` if the user is a sysop.
LEFT JOIN ipblocks ON user_id = ipb_user     -- Adds block information to the list
WHERE ct_tag_id = 577 OR ct_tag_id = 618 OR ct_tag_id = 583    -- Only get edits tagged "RedWarn" or "twinkle"
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 `RedWarn Edits` DESC              -- Order by registration date (latest to oldest)

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...