Untitled query #29060

SQL

SELECT
    YEAR(user_registration) AS RegistrationYear,
    COUNT(DISTINCT actor.actor_id) AS NumberOfUsers,
    COUNT(DISTINCT CASE WHEN editcount_by_revid_100 <= 100 THEN actor_id END) AS Users_1_100EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid_300 BETWEEN 101 AND 300 THEN actor_id END) AS Users_100_300EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid_500 BETWEEN 301 AND 500 THEN actor_id END) AS Users_300_500EPY,
    COUNT(DISTINCT CASE WHEN editcount_by_revid_501 >=501 THEN actor_id END) AS Users_Above_500EPY
FROM
    actor
LEFT JOIN
    (
        SELECT
            rev_actor,
            YEAR(rev_timestamp) AS Year,
            COUNT(DISTINCT rev_id) AS editcount_by_revid,
            SUM(CASE WHEN rev_id <= 100 THEN 1 ELSE 0 END) AS editcount_by_revid_100,
            SUM(CASE WHEN rev_id BETWEEN 101 AND 300 THEN 1 ELSE 0 END) AS editcount_by_revid_300,
            SUM(CASE WHEN rev_id BETWEEN 301 AND 500 THEN 1 ELSE 0 END) AS editcount_by_revid_500,
      		SUM(CASE WHEN rev_id >=501 THEN 1 ELSE 0 END) AS editcount_by_revid_501
        FROM
            revision
        GROUP BY
            rev_actor, Year
    ) AS user_edits ON actor_id = rev_actor
JOIN user ON actor_user = user_id AND actor_user IS NOT NULL
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 RegistrationYear
ORDER BY RegistrationYear;
SQL

Checking query status...
