Untitled query #78909

SQL

SELECT YEAR(user_registration) AS Year_Joined, COUNT(DISTINCT actor_id) AS NumberOfUsers, user_registration,
    COUNT(DISTINCT CASE WHEN editcount_by_revid <= 100 THEN actor_id END) AS Users_1_100EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid BETWEEN 101 AND 300 THEN actor_id END) AS Users_100_300EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid BETWEEN 301 AND 500 THEN actor_id END) AS Users_300_500EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid >=501 THEN actor_id END) AS Users_Above_500EPY
FROM user
JOIN actor ON user_id = actor_user AND actor_user IS NOT NULL
LEFT JOIN (
    SELECT rev_actor, COUNT(DISTINCT(rev_id)) AS editcount_by_revid
    FROM revision
  	WHERE rev_timestamp BETWEEN user.user_registration AND DATE_ADD(YEAR(user.user_registration), INTERVAL 1 YEAR)
    GROUP BY rev_actor
) AS user_edits ON actor_id = rev_actor
LEFT JOIN user_groups ON user_id = ug_user
WHERE user_registration BETWEEN '20100101000000' AND '20231231235959'
AND (LOWER(CAST(user_name AS CHAR)) NOT LIKE '%bot%') AND (ug_group IS NULL OR ug_group != 'bot')
GROUP BY Year_Joined
ORDER BY Year_Joined
SQL

Checking query status...
