Edit counts by time of +sysop, per year

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, '_', ' ')
  JOIN comment on log_comment_id = comment_id
  WHERE log_type = 'rights'
    AND log_action = 'rights'
    AND actor_name NOT LIKE '%bot'
    AND actor_name not like '%Bot'
    AND ( -- pre-Nov-2012 format
          (log_params LIKE '%\n%sysop%' -- new groups include sysop
           AND log_params NOT LIKE '%sysop%\n%' AND NOT log_params LIKE '%bot%' AND NOT comment_text LIKE '%Wikipedia:Bots%') -- 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%' AND NOT log_params LIKE '%bot%' AND NOT comment_text LIKE '%Wikipedia:Bots%') -- old groups don't include sysop
      	  OR -- early sysops 
      	  (comment_text LIKE '%+sysop%' AND NOT comment_text LIKE '%Wikipedia:Bots%' AND NOT log_params LIKE '%bot%')
        )
    AND log_timestamp > 20170000000000 AND log_timestamp < 20180000000000
  GROUP BY actor_name
)
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_userindex 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
  WHERE rev_timestamp < 20160000000000 AND rev_timestamp > 20150000000000
  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
  WHERE ar_timestamp < 20160000000000 AND ar_timestamp > 20150000000000
  GROUP BY a_timestamp, a_name
) sq
GROUP BY a_timestamp, a_name;
SQL

Checking query status...
