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.