Users w/25th AFC review <1 week ago, 1st <6 months ago

Users who made their first edit in the User: or Draft: namespace with an edit summary that looks vaguely like an AFC review less than six months ago and their 25th less than a week ago.

See query 59935 for where I got the edit summary heuristics.  Yes, they're inelegant, and slow, and likely have many false positives, and can't be used to find now-deleted reviews, but until [[WP:AFCH]] adds tags, they're about the best we can do.

For [[WP:RAQ#AfC review's 25th review]] circa 2024 February 24.

SQL

SELECT actor_name
FROM revision_userindex
JOIN page ON rev_page = page_id AND page_namespace IN (2, 118)
JOIN comment_revision ON comment_id = rev_comment_id AND (comment_text LIKE 'Declining submission:%' OR comment_text LIKE 'Rejecting submission:%' OR comment_text LIKE '%Publishing accepted%')
JOIN actor_revision ON actor_id = rev_actor
WHERE rev_actor IN
  (
    SELECT DISTINCT rc_actor
    FROM recentchanges
    JOIN comment_recentchanges ON comment_id = rc_comment_id AND (comment_text LIKE 'Declining submission:%' OR comment_text LIKE 'Rejecting submission:%' OR comment_text LIKE '%Publishing accepted%')
    WHERE rc_namespace IN (2, 118)
      AND rc_timestamp >= @one_week
  )
  AND rev_timestamp >= '200912110419' -- first revision of User:Timotheus_Canens/afchelper4.js, on which AFCH is based
GROUP BY rev_actor
HAVING SUM(rev_timestamp < @six_months) = 0 AND SUM(rev_timestamp < @one_week) < 25 AND COUNT(*) >= 25;

SQL

Checking query status...