Replicas browser and optimizer
This query is marked as a draft
This query has been published
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.
-- ======================================================================= -- 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 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
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in