Untitled query #68806

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 pu.user_name, pu.registered, ec.edit_count, ec.last_edit, fc.filter_count, fc.last_filter
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...
