Users eligible to vote on skwiki

SQL

USE skwiki_p;

-- Policy: https://sk.wikipedia.org/wiki/Wikip%C3%A9dia:Pravidl%C3%A1/Hlasovanie

-- Date and time (UTC) to list eligible voters to, e.g. start of a voting:
SELECT @t:= now();
-- SELECT @t:= '2023-06-07 20:49:00';
-- SELECT @t:= '2015-11-09 11:08:00';

SELECT
user_name, user_registration, user_editcount
FROM
user
WHERE
(user_registration IS NULL OR user_registration < DATE_FORMAT(DATE_SUB(@t, INTERVAL 2 MONTH), '%Y%m%d%H%i%s')) AND
-- 2a) "Je zaregistrovaný vo Wikipédii minimálne po dobu dvoch kalendárnych mesiacov."
-- user.user_registration is NULL for users registered before 2005-12-22
user_editcount >= 200 AND
-- trivial required condition, minimizing number of rows
user_id IN (SELECT DISTINCT actor_user FROM revision r, actor a WHERE a.actor_id = r.rev_actor AND r.rev_timestamp >= DATE_FORMAT(DATE_SUB(@t, INTERVAL 2 MONTH), '%Y%m%d%H%i%s') AND r.rev_timestamp < DATE_FORMAT(@t, '%Y%m%d%H%i%s') GROUP BY actor_user HAVING count(*) >= 50) AND
-- 2b) "Vykonal minimálne 50 úprav vo Wikipédii za posledné dva mesiace pred začiatkom hlasovania."
user_id IN (SELECT DISTINCT actor_user FROM revision r, actor a, page p WHERE p.page_id = r.rev_page AND a.actor_id = r.rev_actor AND p.page_namespace = 0 AND r.rev_timestamp < DATE_FORMAT(@t, '%Y%m%d%H%i%s') GROUP BY actor_user HAVING count(*) >= 200) AND
-- 2c) "Vykonal minimálne 200 úprav v hlavnom mennom priestore pred začiatkom hlasovania."
user_id NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') AND
-- excluding bots
user_name NOT IN ('CommonsDelinker', 'InternetArchiveBot', 'Xqbot')
-- excluding non-flagged bots
ORDER BY
user_name
;
SQL

Checking query status...
