edit bucket distributions (with N edits in timeframe)

Fork of edit bucket distributions (active editors) by KCVelaga (WMF)

This query is marked as a draft This query has been published by KCVelaga (WMF).

SQL

SELECT COUNT(DISTINCT user_name),
    CASE 
        WHEN user_editcount <= 4 THEN '1 to 4 edits'
        WHEN user_editcount >= 5 AND user_editcount <= 99 THEN '5 to 99 edits'
        WHEN user_editcount >= 100 AND user_editcount <= 999 THEN '100 to 999 edits'
        WHEN user_editcount >= 1000 THEN '1000+ edits'
    END AS edit_bucket
FROM revision
JOIN
(
  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)
) all_editors
	ON rev_actor = all_editors.actor_id
WHERE
	rev_timestamp > '20210101000000'
    AND rev_timestamp < '20210331235959'
GROUP BY 
	edit_bucket
-- HAVING
	-- COUNT(rev_id) >= 5

SQL

Checking query status...