SQL
x
SET @one_year_ago = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 YEAR), '%Y%m%d%H%i%s');
SET @three_years_ago = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -3 YEAR), '%Y%m%d%H%i%s');
WITH candidates(c_name, c_editcount) AS
(
SELECT user_name, user_editcount
FROM user
LEFT JOIN user_groups ON ug_user = user_id AND ug_group IN ('sysop', 'bot')
WHERE user_registration <= @three_years_ago
AND user_editcount >= 10000
AND ug_user IS NULL
AND EXISTS (SELECT 1
FROM revision_userindex
JOIN actor_revision ON actor_id = rev_actor
WHERE actor_user = user_id
AND rev_timestamp >= @one_year_ago
LIMIT 1)
)
SELECT CONCAT('TOTAL: ', COUNT(*)) AS 'name', SUM(c_editcount) AS 'edits' FROM candidates
UNION
SELECT * FROM candidates;
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.