If you run a WordPress site you will know that spambots will try and post at an alarming rate to your site. Now there are plenty of spam filters but they don’t always catch users who register and do not post. I have hundreds of these spam users and wanted a quick way to find them.
Well here is an SQL query to find users that have registered but not posted anything – note this is for WordPress used with bbPress
1 2 3
SELECT * FROM wp_users WHERE (SELECT Count(*) FROM wp_posts WHERE wp_posts.post_author = wp_users.id) = 0
Now I just need to find a way to bulk delete these users :)