testing list - Quarry

SQL

/* 
联署提出或上任投票开始前，编辑至少500次；但是在投票开始前120天，编辑少于500次；并在投票开始前90天内至少有1次编辑（不包括任何用户页及用户对话页）
并且
编辑少于3000次
并且
编辑条目少于1500次
*/
SET @VOTE_TIME = 20240616153600;
SET @PRIOR_90_VOTE_TIME = 20240318153600;
SET @PRIOR_120_VOTE_TIME = 20240217153600;

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
),

user_edits_before_vote AS (
    SELECT actor_id, COUNT(*) AS edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    WHERE rev_timestamp < @VOTE_TIME
    GROUP BY actor_id
),

user_edits_before_120_days AS (
    SELECT actor_id, COUNT(*) AS b120edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    WHERE rev_timestamp < @PRIOR_120_VOTE_TIME
    GROUP BY actor_id
),

user_edits_in_last_90_days AS (
    SELECT actor_id, COUNT(*) AS 90d_edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    JOIN page ON page_id = rev_page
    WHERE rev_timestamp BETWEEN @PRIOR_90_VOTE_TIME AND @VOTE_TIME
      AND page_namespace NOT IN (2, 3)
    GROUP BY actor_id
),

user_edits_page_namespace0 AS (
    SELECT actor_id, COUNT(*) AS ns0_edits
    FROM actor
    JOIN all_revision ON actor_id = all_revision.rev_actor
    JOIN page ON page_id = all_revision.rev_page
    WHERE actor_user IS NOT NULL
        AND all_revision.rev_timestamp < @VOTE_TIME
        AND page_namespace = 0
    GROUP BY actor_id
    HAVING ns0_edits >= 1500
)

SELECT actor.actor_id, actor_name, user_registration, user_editcount, uebv.edits, ns0.ns0_edits, ueb120.b120edits, ue90.90d_edits
FROM actor
JOIN user ON actor_user = user_id
JOIN user_edits_before_vote uebv ON actor.actor_id = uebv.actor_id
LEFT JOIN user_edits_before_120_days ueb120 ON actor.actor_id = ueb120.actor_id
LEFT JOIN user_edits_page_namespace0 ns0 ON actor.actor_id = ns0.actor_id
JOIN user_edits_in_last_90_days ue90 ON actor.actor_id = ue90.actor_id
WHERE (uebv.edits >= 500
  AND (ueb120.b120edits IS NULL OR ueb120.b120edits < 500)
       AND ue90.90d_edits > 0) 
  AND uebv.edits < 3000 AND ueb120.b120edits < 1500
ORDER BY actor_name;
SQL

Checking query status...
