zhwiki RFX Eligible Voters with a fix

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

Originally,  Fork of zhwiki RFX Eligible Voters by SCP-2000 

a fix in part 3:   all_revision.rev_timestamp < @PRIOR_120_VOTE_TIME (formerly:  all_revision.rev_timestamp < @VOTE_TIME)
解任投票联署提出或上任投票开始120天前，编辑至少500次

SQL

/*  debugging  

I was able to complete the above SQL query as seen in https://quarry.wmcloud.org/history/84807/911494/884555.   
However, when I run the same query again at https://quarry.wmcloud.org/query/84807.   
I got the error "OperationalError('table resultsets already exists')". How should I fix the error?
I guess that resultsets may be a temporary table that was produced in running my previous  SQL query. 


-- Check the current read-only status
SHOW GLOBAL VARIABLES LIKE 'read_only';

-- Disable read-only mode
SET GLOBAL read_only = OFF;

-- Perform your operations
DROP TABLE IF EXISTS resultsets;

-- Re-enable read-only mode if needed
SET GLOBAL read_only = ON;

*/


-- USE zhwiki_p;

-- Check the current read-only status
SHOW GLOBAL VARIABLES LIKE 'read_only';

-- SHOW TABLES FROM zhwiki_p;
-- SHOW TABLES FROM zhwiki_p;

/*
SELECT
    a.actor_name, COUNT(*) as count_total
  FROM
    page p, revision rf, actor_user a
  WHERE
    rf.rev_page = p.page_id AND rf.rev_parent_id = 0 AND
    a.actor_id = rf.rev_actor AND
    p.page_namespace = 0 AND
    p.page_is_redirect = 0
  GROUP BY
    a.actor_name
  ORDER BY
    count_total desc
  ;
  

SELECT
    a.actor_name, COUNT(*) as count_12m
  FROM
    page p, revision rf, actor_user a
  WHERE
    rf.rev_timestamp >= DATE_SUB(now(), INTERVAL 12 MONTH) AND
    rf.rev_page = p.page_id AND rf.rev_parent_id = 0 AND
    a.actor_id = rf.rev_actor AND
    p.page_namespace = 0 AND
    p.page_is_redirect = 0
  GROUP BY
    a.actor_name
  ORDER BY
    count_12m desc
  ;
*/


SET @VOTE_TIME = 20240616000000;
SET @PRIOR_90_VOTE_TIME = 20240318000000;
SET @PRIOR_120_VOTE_TIME = 20240216000000;

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_registration AS (
    SELECT actor_id, actor_user, actor_name, user_registration
    FROM actor
    JOIN user ON actor_user = user_id
    WHERE user_registration <= @PRIOR_120_VOTE_TIME
)

/*
SELECT part1.actor_name AS user_name
FROM (
    SELECT actor_user, actor_name, 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 < @PRIOR_120_VOTE_TIME
      AND EXISTS (
          SELECT 1
          FROM all_revision AS recent_edits
          JOIN page ON recent_edits.rev_page = page_id
          WHERE recent_edits.rev_actor = actor_id
            AND recent_edits.rev_timestamp BETWEEN @PRIOR_90_VOTE_TIME AND @VOTE_TIME
            AND page_namespace NOT IN (2, 3)
      )
    GROUP BY actor_id
    HAVING edits >= 500
) AS part1

UNION

SELECT part2.actor_name AS user_name
FROM (
    SELECT actor_id, actor_user, actor_name, 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
) AS part2

UNION
*/
SELECT part3.actor_name AS user_name
FROM (
    SELECT actor_id, actor_user, actor_name, COUNT(*) AS ns0_edits
    FROM user_registration
    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
) AS part3

-- ORDER BY user_name;

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...