Edit counts by time of +sysop

Fork of Edit counts by time of +sysop by Cryptic

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

Users who have been granted administrator since mid-2006, and their edit counts before and after promotion.

Edits are counted as live or deleted based on whether they're currently deleted *now*, not at the time of the rights grant.

The rights log didn't exist until 24 Dec 2004, didn't record which rights were changed until 22 Apr 2006, didn't do so consistently until (at least!) Aug 2006, and the format changed significantly on 19 Nov 2012.  Users who have been sysopped more than once will intentionally have more than one row - a trivial example is Northamerica1000, who was sysopped, desysopped, and resysopped all in November 2014 - and early promotions are unavoidably omitted.

Pulling admin promotion dates out of logging necessarily finds their username at the time of promotion; pulling their edits can only be done with their current username.  However, it looks like the *LOGS WERE CHANGED* in some cases (both with manual renames, as evidenced by the "Renamed user [gibberish]" entries, and with SUL, from the ~enwiki entries) - but it wasn't done consistently, which is why some accounts have very few edits counted.  Sheesh.  Accounts with no edits counted at *all* aren't shown, which is fixable but not worth the effort.

I continue to believe that the list of usernames and promotion timestamps really needs to be manually curated to answer the original question with any sort of accuracy - especially since I suspect the intent was to contrast the oldest promotions with the newest - but this should be good enough for a rough overview.

For [[WP:RAQ## of edits each admin had at the time of their RFA]] (how do you even wikilink a section name containing a number sign?) circa 7 Apr 2023.

SQL

WITH admins (a_timestamp, a_name, a_actor) AS
(
  SELECT log_timestamp, actor_name, actor_id
  FROM logging
  JOIN actor_logging ON actor_name = REPLACE(log_title, '_', ' ')
  WHERE log_type = 'rights'
    AND log_action = 'rights'
    AND ( -- pre-Nov-2012 format
          (log_params LIKE '%\n%sysop%' -- new groups include sysop
           AND log_params NOT LIKE '%sysop%\n%') -- old groups don't include sysop
          OR -- post-Nov-2012 format
          (log_params LIKE '%newgroups%sysop%' -- new groups include sysop
           AND log_params NOT LIKE '%oldgroups%sysop%newgroups%') -- old groups don't include sysop
        )
)
SELECT a_timestamp,
       a_name,
       SUM(edits_live_pre) AS edits_live_pre,
       SUM(edits_deleted_pre) AS edits_deleted_pre,
       SUM(edits_live_pre) + SUM(edits_deleted_pre) AS edits_total_pre,
       SUM(edits_live_post) AS edits_live_post,
       SUM(edits_deleted_post) AS edits_deleted_post,
       SUM(edits_live_post) + SUM(edits_deleted_post) AS edits_total_post
FROM
(
  SELECT a_timestamp,
         a_name,
         SUM(CASE WHEN rev_timestamp < a_timestamp THEN 1 ELSE 0 END) AS edits_live_pre,
         SUM(CASE WHEN rev_timestamp < a_timestamp THEN 0 ELSE 1 END) AS edits_live_post,
         0 AS edits_deleted_pre,
         0 AS edits_deleted_post
  FROM admins
  JOIN revision ON rev_actor = a_actor -- revision_userindex *should* be indexed here and revision *shouldn't*, but the reverse is the case, and I have no idea why
  GROUP BY a_timestamp, a_name

  UNION

  SELECT a_timestamp,
         a_name,
         0,
         0,
         SUM(CASE WHEN ar_timestamp < a_timestamp THEN 1 ELSE 0 END),
         SUM(CASE WHEN ar_timestamp < a_timestamp THEN 0 ELSE 1 END)
  FROM admins
  JOIN archive_userindex ON ar_actor = a_actor
  GROUP BY a_timestamp, a_name
) sq
GROUP BY a_timestamp, a_name;

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