Users >= 10 edits in 30 days to articles w/talk in cat, etc

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

Users who have at least 10 edits in the last 30 days to mainspace pages whose talk pages are categorized in [[Category:All WikiProject Medicine articles]], who aren't bots, aren't fully blocked (other than rangeblocks), are ips or have fewer than 10000 edits, and have at least 10% of their mainspace edits to such pages.

(30 days is, conveniently, as long as the recentchanges table lasts.)

For [[WP:RAQ#List of editors for a set of articles]] circa 13 January 2024.

SQL

SELECT actor_name, COUNT(*)
FROM recentchanges
JOIN page ON page_namespace = 1 AND page_title = rc_title
JOIN categorylinks ON cl_from = page_id
JOIN actor_recentchanges ON actor_id = rc_actor
LEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'bot'
LEFT JOIN user ON user_id = actor_user
LEFT JOIN ipblocks_ipindex ON ipb_user = COALESCE(actor_user, 0) AND ipb_address = actor_name AND ipb_sitewide = 1
WHERE rc_namespace = 0
  AND cl_to = 'All_WikiProject_Medicine_articles'
  AND rc_type IN (0, 1) -- edit of existing page, new page - in particular, exclude Wikidata changes (5)
  AND ug_group IS NULL
  AND COALESCE(user_editcount, 0) < 10000
  AND ipb_id IS NULL
GROUP BY rc_actor
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC;

AخA
 
SELECT actor_name, COUNT(*)FROM recentchangesJOIN page ON page_namespace = 1 AND page_title = rc_titleJOIN categorylinks ON cl_from = page_idJOIN actor_recentchanges ON actor_id = rc_actorLEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'bot'LEFT JOIN user ON user_id = actor_userLEFT JOIN ipblocks_ipindex ON ipb_user = COALESCE(actor_user, 0) AND ipb_address = actor_name AND ipb_sitewide = 1WHERE rc_namespace = 0  AND cl_to = 'All_WikiProject_Medicine_articles'  AND rc_type IN (0, 1) -- edit of existing page, new page - in particular, exclude Wikidata changes (5)  AND ug_group IS NULL  AND COALESCE(user_editcount, 0) < 10000  AND ipb_id IS NULLGROUP BY rc_actorHAVING COUNT(*) >= 10ORDER BY COUNT(*) DESC;

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