USE zhwiki_p;

SET @VOTE_TIME = 20241001000000;

SET @PRIOR_90_VOTE_TIME = 20240703000000;

SET @PRIOR_120_VOTE_TIME = 20240603000000;

​

WITH all_revision AS (SELECT rev_id, rev_actor, rev_page, rev_timestamp

                      FROM revision_userindex

                      UNION

                      SELECT ar_id        AS rev_id,

                             ar_actor     AS rev_actor,

                             ar_page_id   AS rev_page,

                             ar_timestamp AS rev_timestamp

                      FROM archive_userindex)

​

SELECT part1.actor_name AS user_name

FROM (SELECT actor_user, actor_name, COUNT(*) AS edits

      FROM actor

               JOIN all_revision ON actor_id = all_revision.rev_actor

               JOIN user ON actor_user = user.user_id

      WHERE actor_user IS NOT NULL

        AND user.user_registration < @PRIOR_120_VOTE_TIME

        AND all_revision.rev_timestamp < @VOTE_TIME

      GROUP BY actor_id

      HAVING edits >= 3000) AS part1

ORDER BY user_name;