sandbox 3 - Quarry

SQL

WITH active_editors AS (
  SELECT rev_actor, COUNT(rev_id) AS recent_edit_count
  FROM revision
  WHERE
	rev_timestamp > '20210101000000'
	AND rev_timestamp < '20210331235959'
  GROUP BY rev_actor
  HAVING COUNT(rev_id) >= 5),
  
  non_bot_editors AS (
  SELECT actor_id, user_id, user_name, user_editcount
  FROM actor
  JOIN user
  ON actor.actor_user = user.user_id
  LEFT JOIN user_groups
  ON ug_user = user_id
  WHERE user_editcount>0
  AND (ug_group NOT IN ('bot', 'flow-bot')
       OR ug_group IS NULL)),
  
  non_bot_active_editors AS (
    SELECT 
    	non_bot_editors.user_id AS user_id,
    	non_bot_editors.user_editcount AS total_edit_count
    	active_editors.recent_edit_count AS recent_edit_count
    FROM active_editors
    INNER JOIN active_editors
    ON active_editors.rev_actor = non_bot_editors.actor_id)
    
SELECT COUNT(DISTINCT user_id),
	CASE
        WHEN total_edit_count <= 4 THEN '1 to 4 edits'
        WHEN total_edit_count >= 5 AND total_edit_count <= 99 THEN '5 to 99 edits'
        WHEN total_edit_count >= 100 AND total_edit_count <= 999 THEN '100 to 999 edits'
        WHEN total_edit_count >= 1000 THEN '1000+ edits'
    END AS edit_bucket
FROM non_bot_active_editors
GROUP BY edit_bucket
SQL

Checking query status...
