Untitled query #69106

SQL

SELECT CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', afl.afl_user_text) AS 'User',
TIMESTAMP(MAX(afl.afl_timestamp)) AS 'Registered',
COUNT(r.rev_id) AS 'Edit count',
TIMESTAMP(MAX(r.rev_timestamp)) AS 'Last edit'
FROM abuse_filter_log afl
INNER JOIN user u ON afl.afl_user_text = u.user_name
INNER JOIN ipblocks ipb ON u.user_id = ipb.ipb_user
INNER JOIN actor_user a ON u.user_id = a.actor_user
INNER JOIN revision_userindex r ON a.actor_id = r.rev_actor
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)
ORDER BY MAX(afl.afl_timestamp) DESC;
SQL

Checking query status...
