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

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 `XC?`,            -- Extended confirmed
    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
    COUNT(CASE WHEN ct_tag_id = 583 THEN 1 END) as `Twinkle Edits`,                  -- Total Twinkle-tagged edits
    COUNT(CASE WHEN ct_tag_id = 583 THEN 1 END)/user_editcount * 100 as `Twinkle %`, -- Percentage of TW-tagged edits vs. total edits
    -- RedWarn to Twinkle edit ratio (currently testing this)
    COUNT(CASE WHEN ct_tag_id = 577 THEN 1 END)/COUNT(CASE WHEN ct_tag_id = 583 THEN 1 END) as `RW ÷ TW`,
    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
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 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 user_registration 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...