table columns testing

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

/*  debugging  

*/
/* 
联署提出或上任投票开始前(@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 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 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
)

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_user, actor_name, user_registration, user_editcount, uebv.edits, ns0.ns0_edits, ueb120.b120edits
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.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) 
*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
