test1 - Quarry

SQL

SELECT u.user_id, u.user_name, COUNT(DISTINCT r.rev_id) as total_mainspace_edits,
    COUNT(CASE WHEN r.rev_timestamp BETWEEN '20230925000000' AND '20240924235959' THEN 1 END) as live_edits
FROM user u
JOIN actor a ON a.actor_user = u.user_id
JOIN revision_userindex r ON r.rev_actor = a.actor_id
JOIN page p ON p.page_id = r.rev_page
LEFT JOIN user_groups ug ON u.user_id = ug.ug_user AND ug.ug_group = 'bot'
LEFT JOIN block b ON b.bl_target = u.user_id AND (b.bl_expiry IS NULL OR b.bl_expiry > NOW())
WHERE u.user_registration <= '20240824235959' -- Account created before 24 August 2024
  AND p.page_namespace = 0 -- Only mainspace edits
  AND ug.ug_group IS NULL -- Exclude bots
  AND b.bl_id IS NULL -- Ensure the user is not currently blocked (no active block)
GROUP BY u.user_id
HAVING total_mainspace_edits >= 150 -- 150 mainspace edits by 24 September 2024
   AND live_edits >= 10 -- At least 10 live edits between 25 September 2023 and 24 September 2024
;
SQL

Checking query status...
