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

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

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

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_id, 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
    AND ug_group IS NULL
    AND COALESCE(user_editcount, 0) < 10000
    AND ipb_id IS NULL
  GROUP BY rc_actor
  HAVING COUNT(*) >= 10

SQL

Checking query status...