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.