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

Users who have at least 10 edits in the last 30 days to non-talk 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 non-talk 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

WITH wpedits (wp_actor, wp_name, wp_edits_in_wp) AS
(
  SELECT actor_id, actor_name, COUNT(*)
  FROM recentchanges
  JOIN page ON page_namespace = rc_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 % 2 = 0
    AND cl_to = 'All_WikiProject_Medicine_articles'
    AND rc_type IN (0, 1) -- edit of existing page, new page
    AND ug_group IS NULL
    AND COALESCE(user_editcount, 0) < 10000
    AND ipb_id IS NULL
  GROUP BY rc_actor
  HAVING COUNT(*) >= 10
)
SELECT wp_name AS `user`,
       wp_edits_in_wp AS `project edits`,
       COUNT(*) AS `total non-talk edits`,
       100 * wp_edits_in_wp / COUNT(*) AS `% in project`
FROM wpedits
JOIN recentchanges_userindex ON rc_actor = wp_actor
WHERE rc_namespace % 2 = 0
  AND rc_type IN (0, 1)
GROUP BY wp_actor
HAVING `% in project` >= 10
ORDER BY `project edits` DESC;

SQL

Checking query status...