This query is marked as a draft This query has been published by Aram.

SQL

x
 
USE ckbwiki_p;
WITH ActiveBotsData 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 activity threshold
)
SELECT
    ROW_NUMBER() OVER (ORDER BY LastEditTimestamp DESC) AS '#',
    Username,
    DATE_FORMAT(LastEditTimestamp, '%Y %M %e') AS LastEditDate,
    NumberOfEdits,
    BotGroups,
    DATE_FORMAT(RegistrationTimestamp, '%Y %M %e') AS RegistrationDate
FROM ActiveBotsData
ORDER BY LastEditTimestamp DESC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Query status: complete

Executed in 4.70 seconds as of Sun, 26 Nov 2023 21:53:32 UTC.