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

SQL

x
 
/**
 * Admin name
 * Count of edits
 *
 * Does not include log actions yet
 * Timestamp of their 10th most recent **log entry** that requires admin rights, does not account for other admin actions
 * DOES NOT ACCOUNT FOR STEWARD ACTIONS BEING LOGGED AS ADMIN ACTIONS
 *
 * Log entries that count:
 * - abusefilter/*
 * - block/*
 * - delete/delete, /revision, /restore, /event
 * - import/*
 * - rights/rights
 * - protect/protect, /modify
 *
 * Not sure about
 * - massmessage
 * - pagetranslation
 * - pagelang
 * - patrol
 */
USE metawiki_p;
set @start = '20200401000000';
set @end = '20201001000000';
-- Admins with 0 edits don't show up in the revision query at all, so query them separately
SELECT
    ug.ug_user,
    a.actor_id,
    a.actor_name,
    0 AS editCount
FROM
    user_groups ug
JOIN
    actor_user a
    ON a.actor_user = ug.ug_user
WHERE
    ug.ug_group = 'sysop'
    AND NOT EXISTS (
        SELECT 1
        FROM revision_userindex rev
        WHERE rev.rev_actor = a.actor_id
        AND rev.rev_timestamp > @start
        AND rev.rev_timestamp <= @end
        LIMIT 1
    );
-- Admins with edits, ordered by edit count
SELECT
    ug.ug_user,
    a.actor_id,
    a.actor_name,
    COUNT(rev.rev_id) AS editCount
FROM
    user_groups ug
JOIN
    actor_user a
    ON a.actor_user = ug.ug_user
LEFT JOIN
    revision_userindex rev
    ON rev.rev_actor = a.actor_id
WHERE
    ug.ug_group = 'sysop'
    AND rev.rev_timestamp > @start
    AND rev.rev_timestamp <= @end
GROUP BY
    a.actor_id
ORDER BY
    editCount ASC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...