Users w/ >=10k edits, registered >=3ya, 1 live edit <=1ya

Fork of Users w/ >=10k edits, registered >=3ya, 1 live edit <=1ya by Cryptic

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

SQL

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 -1 YEAR), '%Y%m%d%H%i%s');

WITH candidates(c_name, c_editcount,c_registration) AS
(
  SELECT user_name, user_editcount, user_registration
  FROM user
  WHERE user_registration <= @three_years_ago
    AND user_editcount >= 1000
    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','reg' FROM candidates
UNION
SELECT * FROM candidates;

SQL

Checking query status...