hiwiki articleles (nov 2023)

SQL

SELECT 
	edit_bucket,
	ROUND(AVG(total_weekly_bytes)) as avg_weekly_bytes
FROM
(
  SELECT 
    SUM(rev_len) AS total_weekly_bytes,
    WEEKOFYEAR(rev_timestamp) as week_n,
    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 IS NULL
  OR ug_group NOT IN ('bot', 'flow-bot'))
) total_edits
    ON rev_actor = total_edits.actor_id
WHERE
    rev_timestamp > '20210101000000'
    AND rev_timestamp < '20211231235959'
  GROUP BY WEEKOFYEAR(rev_timestamp), edit_bucket
) AS bytes_week_bucket
GROUP BY bytes_week_bucket.edit_bucket
SQL

Checking query status...
