Total RedWarn/Ultraviolet edits, past month

Fork of RedWarn/Ultraviolet users, past month by Sportzpikachu

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

Forked from Chlod's fork of AntiCompositeNumber's original query

Tag ids: 577 = RW, 618 = UV, 583 = TW

SQL

-- ==================================================================================
-- 2023-12-14 - Updated by [[User:Sportzpikachu]]
--     * Check UV instead of TW
--     * Check for edits in past month
--     * Require at least 1 RW/UV edit in past month
--     * Additional query for count of users with more UV edits than RW in past month
-- Improved by RW developer Chlod, forked from AntiCompositeNumber's original
-- RW users script. Both versions released under CC0 (per Quarry terms).

SET @RW_TAG_ID := 577, @UV_TAG_ID := 618, @TW_TAG_ID := 583; -- 577 = RW, 618 = UV, 583 = TW

SELECT
	`actor_name` as `Username`,                  -- Editor username
    `user_editcount` as `Edit Count`,            -- Editor total edit count
    COUNT(rev_id) as `Edit Count (1mo)`,
    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 = @RW_TAG_ID, 1, NULL)) as `RedWarn Edits`,                  -- Total RedWarn-tagged edits
    COUNT(IF(ct_tag_id = @RW_TAG_ID, 1, NULL))/COUNT(rev_id) * 100 as `RedWarn %`, -- Percentage of RW-tagged edits vs. total edits
    MAX(IF(ct_tag_id = @RW_TAG_ID, DATE_FORMAT(`rev_timestamp`, "%Y-%m-%d %H:%i:%s"), NULL)) as `Last RW edit`,       -- Get latest RW edit
    COUNT(IF(ct_tag_id = @UV_TAG_ID, 1, NULL)) as `UV Edits`,                  -- Total UV-tagged edits
    COUNT(IF(ct_tag_id = @UV_TAG_ID, 1, NULL))/COUNT(rev_id) * 100 as `UV %`, -- Percentage of UV-tagged edits vs. total edits
    MAX(IF(ct_tag_id = @UV_TAG_ID, DATE_FORMAT(`rev_timestamp`, "%Y-%m-%d %H:%i:%s"), NULL)) as `Last UV edit`,
    -- RedWarn to UV edit ratio
    COUNT(IF(ct_tag_id = @RW_TAG_ID, 1, NULL))/GREATEST(COUNT(IF(ct_tag_id = @UV_TAG_ID, 1, NULL)), 1) as `RW ÷ UV`, -- div by 1 if UV is 0
    COUNT(IF(ct_tag_id = @UV_TAG_ID, 1, NULL))/GREATEST(COUNT(IF(ct_tag_id = @RW_TAG_ID, 1, NULL)), 1) as `UV ÷ RW`,
    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 = @RW_TAG_ID OR ct_tag_id = @UV_TAG_ID) AND rev_timestamp > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH)  
GROUP BY actor_name                          -- Squash the table based on the username
HAVING `RedWarn Edits` > 0 OR `UV Edits` > 0                  -- Only include users who have made at least 1 RedWarn/UV edit
ORDER BY `UV Edits` DESC;

SELECT
    SUM(RW_Count),
    SUM(UV_Count)
FROM (
    SELECT
        COUNT(IF(ct_tag_id = @RW_TAG_ID, 1, NULL)) as RW_Count,
        COUNT(IF(ct_tag_id = @UV_TAG_ID, 1, NULL)) as UV_Count
    FROM revision_userindex
    JOIN change_tag ON ct_rev_id = rev_id
    JOIN actor_revision ON rev_actor = actor_id
    WHERE (ct_tag_id = @RW_TAG_ID OR ct_tag_id = @UV_TAG_ID) AND rev_timestamp > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH)
    GROUP BY actor_name
    HAVING RW_Count > 0 OR UV_Count > 0
) AS counts;

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