This query is marked as a draft This query has been published by Cryptic.

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.

Checking query status...