Automoderator trwiki edits

This query is marked as a draft This query has been published by KCVelaga (WMF).
SQL

-- =====================================================================================================================
-- fields/tables related to reverts made by Automoderator are prefixed with amr_ (automoderator's reverts)
-- fields/tables related to the edit which Automoderator reverted are prefixed with revr_ (reverted revisions)
-- fields/tables related to edits which reverted Automoderator's reverts are prefixed with revamr_ (reverted AN reverts)
-- =====================================================================================================================

WITH automod_reverts AS (
  SELECT
    -- =======================================================================
    -- data related to reverts made by Automoderator (prefix: amr_)
    -- =======================================================================
    'trwiki' AS wiki_db,
    0 AS is_small_wiki, -- https://phabricator.wikimedia.org/T372280; https://meta.wikimedia.org/wiki/Special:WikiSets/7
    amr_rev.rev_id AS amr_rev_id,
    amr_rev.rev_timestamp AS amr_rev_dt,
    DATE(amr_rev.rev_timestamp) AS amr_rev_date,
    MAX(
         CASE
             WHEN amr_ctag_def.ctd_name = 'mw-reverted' THEN TRUE
             ELSE FALSE
         END
    ) AS is_amr_reverted,
    amr_rev.rev_parent_id AS amr_rev_parent_id,
    UNIX_TIMESTAMP(amr_rev.rev_timestamp) - UNIX_TIMESTAMP(revr_rev.rev_timestamp) AS amr_time_to_revert_sec,
    -- =========================================================================
    -- data related to edits that were reverted by Automoderator (prefix: revr_)
    -- =========================================================================
    revr_rev.rev_id AS revr_rev_id,
    revr_actor.actor_name AS revr_actor_name,
    revr_rev.rev_timestamp AS revr_rev_dt,
    DATE(revr_rev.rev_timestamp) AS revr_rev_date,
    CASE
        WHEN revr_actor.actor_user IS NULL THEN 'anonymous'
        WHEN revr_user.user_is_temp THEN 'temporary'
        WHEN 
            revr_actor.actor_user IS NOT NULL 
            AND NOT revr_user.user_is_temp
            AND revr_user.user_editcount < 50 
        THEN 'newcomer'
        ELSE 'other_registered'
    END AS revr_user_type,
    CASE
        WHEN (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) THEN NULL
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount = 0 THEN '0'
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount BETWEEN 1 AND 5 THEN '1-5'
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount BETWEEN 6 AND 99 THEN '6-99'
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount BETWEEN 100 AND 999 THEN '100-999'
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount BETWEEN 1000 AND 4999 THEN '1000-4999'
        WHEN NOT (revr_actor.actor_user IS NULL OR revr_user.user_is_temp) AND revr_user.user_editcount > 5000 THEN '5000+'
        ELSE 'misc'
    END AS revr_user_editcount_bucket
  FROM
    revision amr_rev
  JOIN
    change_tag amr_ctag
    ON amr_rev.rev_id = amr_ctag.ct_rev_id
  JOIN
    change_tag_def amr_ctag_def
    ON amr_ctag.ct_tag_id = amr_ctag_def.ctd_id
  JOIN
    actor amr_actor
    ON amr_rev.rev_actor = amr_actor.actor_id
  JOIN
    revision revr_rev
    ON amr_rev.rev_parent_id = revr_rev.rev_id
  JOIN
    actor revr_actor
    ON revr_rev.rev_actor = revr_actor.actor_id
  LEFT JOIN
    user revr_user
    ON revr_actor.actor_user = revr_user.user_id
  WHERE
    amr_actor.actor_name = 'Otomoderatör'
    AND DATE(amr_rev.rev_timestamp) > DATE('2024-5-31')
  GROUP BY
    amr_rev.rev_id,
    wiki_db
),

-- ==============================================================================
-- data related to reverted Automoderator reverts, if applicable (prefix: revam_)
-- ==============================================================================
reverted_am_reverts AS (
    SELECT
        amr.*,
        revam_rev.rev_id AS revam_rev_id,
        revam_rev.rev_timestamp AS revam_rev_dt,
        DATE(revam_rev.rev_timestamp) AS revam_rev_date,
        UNIX_TIMESTAMP(revam_rev.rev_timestamp) - UNIX_TIMESTAMP(amr.amr_rev_dt) AS revam_time_to_revert_sec,
        revam_actor.actor_name AS revam_actor_name,
        CASE
            WHEN revam_actor.actor_user IS NULL THEN 'anonymous'
            WHEN revam_user.user_is_temp THEN 'temporary'
            WHEN 
                revam_actor.actor_user IS NOT NULL 
                AND NOT revam_user.user_is_temp
                AND revam_user.user_editcount < 50 
            THEN 'newcomer'
            ELSE 'other_registered'
        END AS revam_user_type,
        -- a proxy for potential false positve rate, if an Automoderator's revert is reverted back
        -- the revert should be made by a different user, unless they other_registered (i.e. not anonymous, temporary or newcomers)
        CASE
            WHEN 
                amr.revr_actor_name = revam_actor.actor_name 
                AND NOT amr.revr_user_type = 'other_registered' 
            THEN FALSE
            ELSE TRUE
        END AS is_potential_false_positive
    FROM
        revision revam_rev
    JOIN
        automod_reverts amr
        ON revam_rev.rev_parent_id = amr.amr_rev_id
    JOIN
        actor revam_actor
        ON revam_rev.rev_actor = revam_actor.actor_id
    LEFT JOIN
        user revam_user
        ON revam_actor.actor_user = revam_user.user_id
    WHERE
        is_amr_reverted
)

SELECT 
    * ,
    NULL AS revam_rev_id,
    NULL AS revam_rev_dt,
    NULL AS revam_rev_date,
    NULL AS revam_time_to_revert_sec,
    NULL AS revam_actor_name,
    NULL AS revam_user_type,
    NULL AS is_potential_false_positive
FROM 
    automod_reverts 
WHERE 
    NOT is_amr_reverted
UNION ALL
SELECT 
    * 
FROM 
    reverted_am_reverts
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...
