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.