table columns testing

SQL

/*  debugging  

*/
/* 
Purpose: 
联署提出或上任投票开始前(@VOTE_TIME)，编辑至少500次；但是在投票开始前120天(@PRIOR_120_VOTE_TIME)，编辑少于500次；并在投票开始前90天(@PRIOR_90_VOTE_TIME)内至少有1次编辑（不包括任何用户页及用户对话页）
*/
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 edits
    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 edits_in_90_days
    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
)

SELECT actor.actor_id, actor_user, actor_name, user_registration, user_editcount, uebv.edits, ueb120.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
JOIN user_edits_in_last_90_days ue90 ON actor.actor_id = ue90.actor_id
WHERE uebv.edits >= 500
  AND (ueb120.edits IS NULL OR ueb120.edits < 500)
  AND ue90.edits_in_90_days > 0
ORDER BY actor_name;
-- Executed in 3384.90 seconds as of Mon, 22 Jul 2024 13:28:12 UTC. Resultset (106 rows)


   
/*


SELECT actor_id, actor_user, actor_name, user_registration, user_editcount, COUNT(*) AS edits
    FROM user_registration
    JOIN all_revision ON actor_id = all_revision.rev_actor
    WHERE actor_user IS NOT NULL
      AND all_revision.rev_timestamp < @VOTE_TIME
    GROUP BY actor_id
    HAVING edits >= 3000
    ORDER BY actor_name;
-- Executed in 1890.95 seconds as of Mon, 22 Jul 2024 00:22:19 UTC. Resultset (2203 rows)

SET @PRIOR_120_VOTE_TIME = 20240217153600;
SELECT  user_id, user_name, user_registration, user_editcount           
FROM user
WHERE user_registration <= @PRIOR_120_VOTE_TIME 
AND user_editcount >= 3000 
ORDER BY user_name;
-- Executed in 3.37 seconds as of Sun, 21 Jul 2024 23:39:52 UTC.  Resultset (2205 rows) 
*/
SQL

Checking query status...
