Te Wiki Admin - sysop Activity

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
        )
    AND log_timestamp > 20180101000000 -- 2018 to present
)
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
ORDER BY a_timestamp DESC; -- recent RFAs first
SQL

Checking query status...
