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;