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

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.

Checking query status...