Fork of Edit counts by time of +sysop by Ixtal
This query is marked as a draft This query has been published by Ixtal.

SQL

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

Checking query status...