Delete Spam Users from YetAnotherForum

I use YetAnotherForum (YAF) for my Forums here.   Like others, I get hundreds of spam users created each month.  I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum and isn’t in the Active list.

 

-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(SELECT u.UserID 
FROM dbo.yaf_User u
WHERE u.LastVisit < getdate() -1
AND u.NumPosts = 0)
AND UserID NOT IN
(SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)

AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)

--
-- now delete the User rows.
--

DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN 
(SELECT m.UserId 
FROM dbo.yaf_Message m)