SET @last_year = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 YEAR), '%Y%m%d%H%i%s');

SELECT actor_name,

       SUM(rev_timestamp LIKE '202404%') AS '2024-04',

       SUM(rev_timestamp LIKE '202405%') AS '2024-05',

       SUM(rev_timestamp LIKE '202406%') AS '2024-06',

       SUM(rev_timestamp LIKE '202407%') AS '2024-07',

       COUNT(*) / 4 AS 'average per month'

FROM revision

JOIN actor_revision ON actor_id = rev_actor

JOIN user ON user_id = actor_user

WHERE rev_timestamp BETWEEN '202404' AND '202408'

  AND user_editcount >= 10000

  AND (user_registration IS NULL -- there's some users where this never got backfilled; all that I know of registered before 2006, so include

       OR user_registration <= @last_year)

  AND NOT EXISTS (SELECT 1 FROM user_groups WHERE ug_user = actor_user AND ug_group = 'sysop')

GROUP BY rev_actor

HAVING SUM(rev_timestamp LIKE '202404%') >= 1000

    OR SUM(rev_timestamp LIKE '202405%') >= 1000

    OR SUM(rev_timestamp LIKE '202406%') >= 1000

    OR SUM(rev_timestamp LIKE '202407%') >= 1000

ORDER BY actor_name ASC;