Recent activity by accounts with promo-flagged usernames

Finds users who have tripped abuse filter 54 ("promotional, group, organization, or company username (Oshwah)") within the past year who have less than 30 contributions overall and at least one contribution or other filter trip within the past three days.

To do:
-Flag users who have triggered autobiography, COI, or link containing username abuse filters

SQL

SELECT CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', afl.afl_user_text) AS 'User',
MAX(afl.afl_timestamp) AS 'Registered',
COUNT(r.rev_id) AS 'Edit count',
TIMESTAMP(MAX(r.rev_timestamp)) AS 'Last edit',
COUNT(afl2.afl_id) AS 'Filter trip count',
TIMESTAMP(MAX(afl2.afl_timestamp)) AS 'Last filter trip'
FROM abuse_filter_log afl
INNER JOIN user u ON afl.afl_user_text = u.user_name
LEFT JOIN ipblocks ipb ON u.user_id = ipb.ipb_user
LEFT JOIN actor_user a ON u.user_id = a.actor_user
LEFT JOIN revision_userindex r ON a.actor_id = r.rev_actor
LEFT JOIN abuse_filter_log afl2 ON u.user_id = afl2.afl_user
WHERE afl.afl_filter_id = 54
AND afl.afl_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND afl.afl_user = 0 -- Ignore accounts created by already-registered users
AND ipb.ipb_id IS NULL
GROUP BY afl.afl_user_text
HAVING COUNT(r.rev_id) <= 30
AND 
(
 MAX(r.rev_timestamp) > DATE_SUB(NOW(), INTERVAL 3 DAY)
 OR
 MAX(afl2.afl_timestamp) > DATE_SUB(NOW(), INTERVAL 3 DAY)
)
ORDER BY MAX(afl.afl_timestamp) DESC;

SQL

Checking query status...