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

WITH promo_users AS (
SELECT u.user_id, afl.afl_user_text AS user_name, MAX(afl.afl_timestamp) AS registered
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
WHERE afl.afl_filter_id = 54
AND afl.afl_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND afl_user = 0
AND ipb.ipb_id IS NULL
GROUP BY afl.afl_user_text
),
edit_count AS (
SELECT pu.user_id, COUNT(r.rev_id) AS edit_count, MAX(r.rev_timestamp) AS last_edit
FROM promo_users pu
LEFT JOIN actor_user a ON pu.user_id = a.actor_user
LEFT JOIN revision_userindex r ON a.actor_id = r.rev_actor
GROUP BY pu.user_id
),
filter_count AS (
SELECT pu.user_id, COUNT(afl.afl_id) AS filter_count, MAX(afl.afl_timestamp) AS last_filter
FROM promo_users pu
LEFT JOIN abuse_filter_log afl ON pu.user_id = afl.afl_user
GROUP BY pu.user_id
)

SELECT 
CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', pu.user_name) AS 'User',
TIMESTAMP(pu.registered) AS 'Registered',
ec.edit_count AS 'Edit count',
TIMESTAMP(ec.last_edit) AS 'Last edit',
fc.filter_count AS 'Filter trip count',
TIMESTAMP(fc.last_filter) AS 'Last filter trip'
FROM promo_users pu
INNER JOIN edit_count ec ON pu.user_id = ec.user_id
INNER JOIN filter_count fc ON pu.user_id = fc.user_id
WHERE ec.edit_count <= 30
AND (ec.last_edit > DATE_SUB(NOW(), INTERVAL 3 DAY) OR fc.last_filter > DATE_SUB(NOW(), INTERVAL 3 DAY))
ORDER BY pu.registered DESC

SQL

Checking query status...