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;
*/
SELECT
    actor_name AS name,
    SUM(CASE WHEN YEAR(creation_date) = 2015 THEN 1 ELSE 0 END) AS `2015`,
    SUM(CASE WHEN YEAR(creation_date) = 2016 THEN 1 ELSE 0 END) AS `2016`,
    SUM(CASE WHEN YEAR(creation_date) = 2017 THEN 1 ELSE 0 END) AS `2017`,
    SUM(CASE WHEN YEAR(creation_date) = 2018 THEN 1 ELSE 0 END) AS `2018`,
    SUM(CASE WHEN YEAR(creation_date) = 2019 THEN 1 ELSE 0 END) AS `2019`,
    SUM(CASE WHEN YEAR(creation_date) = 2020 THEN 1 ELSE 0 END) AS `2020`,
    SUM(CASE WHEN YEAR(creation_date) = 2021 THEN 1 ELSE 0 END) AS `2021`,
    SUM(CASE WHEN YEAR(creation_date) = 2022 THEN 1 ELSE 0 END) AS `2022`,
    SUM(CASE WHEN YEAR(creation_date) = 2023 THEN 1 ELSE 0 END) AS `2023`
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
WHERE creation_date = (
    SELECT MIN(rev_timestamp)
    FROM revision
    WHERE rev_page = first_revisions.page_id
)
GROUP BY actor_name
ORDER BY COUNT(*) DESC;
SQL

Checking query status...
