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 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
    actor
LEFT JOIN
    (
        SELECT rev_actor, YEAR(rev_timestamp) AS Year,
            COUNT(DISTINCT rev_id) AS editcount_by_revid
        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...
