Inactive bot statistics (ckbwiki)

Retrieves inactive bots that haven't made any edits (on Central Kurdish Wikipedia) in the last 30 days.

For active bots with local edit counter, see https://quarry.wmcloud.org/query/77632
For active bots with live edit counter, see https://quarry.wmcloud.org/history/77632/816452/792431

SQL

USE ckbwiki_p;

WITH InactiveBotsData AS (
    SELECT
        actor_name AS Username,
        MAX(rev_timestamp) AS LastEditTimestamp,
        user_editcount AS NumberOfEdits,
        GROUP_CONCAT(DISTINCT actor_user_groups.ug_group) AS BotGroups,
        user_registration AS RegistrationTimestamp
    FROM revision
    JOIN actor ON actor_id = rev_actor
    JOIN user ON actor_user = user_id
    LEFT JOIN user_groups AS actor_user_groups ON user_id = actor_user_groups.ug_user
    WHERE actor_user_groups.ug_group LIKE '%bot%'
    GROUP BY Username
    HAVING LastEditTimestamp < DATE_FORMAT(NOW() - INTERVAL 30 DAY, '%Y%m%d%H%i%s') -- Change '30 DAY' to the desired inactivity threshold
)

SELECT
    ROW_NUMBER() OVER (ORDER BY LastEditTimestamp ASC) AS '#',
    Username,
    DATE_FORMAT(LastEditTimestamp, '%Y %M %e') AS LastEditDate,
    NumberOfEdits,
    BotGroups,
    DATE_FORMAT(RegistrationTimestamp, '%Y %M %e') AS RegistrationDate
FROM InactiveBotsData
ORDER BY LastEditTimestamp ASC;

SQL

Checking query status...