Untitled query #78622

SQL

SELECT YEAR(creation_date) AS creation_year, actor_name, COUNT(*) AS category_count
FROM (
    SELECT page_id, MIN(rev_timestamp) AS creation_date, actor_name
    FROM page
    JOIN revision ON page_id = rev_page
    INNER JOIN actor ON rev_actor = actor_id
    WHERE page_namespace = 14 -- Category namespace
    AND (
        UPPER(actor_name) LIKE UPPER("%BOT") COLLATE utf8_general_ci
        OR actor_name LIKE "%بوت%" COLLATE utf8_general_ci
        OR actor_name IN (
            SELECT user_name
            FROM user_groups
            INNER JOIN user ON user_id = ug_user
            WHERE ug_group = "bot"
        )
    )
    GROUP BY page_id, actor_name
) AS first_revisions
GROUP BY creation_year, actor_name
ORDER BY creation_year, actor_name;
SQL

Checking query status...
