SQL
AخA
SET @one_week = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 WEEK), '%Y%m%d%H%i%s');
WITH past_week_reviewers(pwr_name, pwr_edits) AS
(
SELECT DISTINCT actor_name, user_editcount
FROM recentchanges
JOIN comment_recentchanges ON comment_id = rc_comment_id AND (comment_text LIKE 'Declining submission:%' OR comment_text LIKE 'Rejecting submission:%' OR comment_text LIKE '%Publishing accepted%')
JOIN actor_recentchanges ON actor_id = rc_actor
JOIN user ON user_id = actor_user
WHERE rc_namespace IN (2, 118)
AND rc_timestamp >= @one_week
ORDER BY user_editcount ASC
)
SELECT 'TOTAL (editcount < 50000)' AS name, SUM(CASE WHEN pwr_edits < 50000 THEN pwr_edits ELSE 0 END) AS 'edit count'
FROM past_week_reviewers
UNION
SELECT 'TOTAL (editcount < 100000)', SUM(CASE WHEN pwr_edits < 100000 THEN pwr_edits ELSE 0 END)
FROM past_week_reviewers
UNION
SELECT 'TOTAL (editcount < 300000)', SUM(CASE WHEN pwr_edits < 300000 THEN pwr_edits ELSE 0 END)
FROM past_week_reviewers
UNION
SELECT 'TOTAL', SUM(pwr_edits)
FROM past_week_reviewers
UNION
SELECT NULL, NULL
UNION
SELECT *
FROM past_week_reviewers;
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.