Quote:
Originally Posted by Neal Stevens
Yes, sir, my intention is to look back at members who joined since Dec 1, 2010, have 0 posts, have a signature, and their last activity was 24 hours from their join date. We find many of the sig spammers simply make an account and never post or log in again.
I want to minimize deleting actual people, so if a guy joined Dec 20 and his last activity was Dec 23, then he stays. I don't mind a guy being inactive for 6 months, as long as he is not a spammer. Does that make sense?
Yes, I am studying up on the JOIN command, internal and external. I'm a real noob with SQL so I have to build this query from scratch. Anything you have to say on the subject is welcome, thanks, Penguin.  |
Ok, this makes sense, if the spammer doesn't log in a second time.(didn't check out the spam hunt thread yet to look at actual spammer accounts
)
EDIT: Found this one while randomly looking for spammers:
http://www.subsim.com/radioroom/member.php?u=276634 He logged in 3 days after he registered , however most found had their last activity on the date of registration.
So the command to get all the infos about this account would look like this, this is the explicit statement, you can make it shorter, but this is better for the comprehension
SELECT *
(all infos, or whatever you need)
FROM User AS U1 INNER JOIN Sigparsed AS S1
(just abbreviations to type less)
ON U1.UserID = S1.UserId
(UserId, or something simular would be your unique key that you can find in both tables)
WHERE U1.Joindate >1291165261
AND (U1.Lastactivity - U1.joindate ) < 86400
AND U1.Totalposts < 2
AND S1.Signatureparsed NOT LIKE ' '
AND S1.signatureparsed IS NOT NULL
ODER BY U1.Username ;
Here is some material to learn SQL: A lecture by David Malan, who teaches at a little, unknown school on the East Coast. I think he is very articulate and intelligible:
http://cs75.tv/2010/fall/#l=lectures...res/4/lecture4 At the lower left corner you'll find also the second part (lecture5)
If you are more of a beginner, there is also a SQL lecture in the introduction to Computer Science class found here:
http://cs50.tv/2010/fall/ If a lecture is broken you may want to play with the course dates in the upper drop-down menu to access courses from previous years.