What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 133 Stack Exchange communities.

Something like this (in pseudocode):

 if answers > 3 and answers-with-url / total-answers > .5 
 then you-are-a-spammer 

Does anyone know how to do this?

share|improve this question
8  
You could just use the User.IsASpammer field, but they don't include it in the data dumps :( –  Michael Mrozek Feb 14 '11 at 21:15
4  
</couldn't-resist> –  SLaks Feb 14 '11 at 21:29
    
Matching a URL inside a text field in SQL will be difficult if possible. –  SLaks Feb 14 '11 at 21:30
    
@SLaks a primitive test for http:// and www. will create some false positives but my gut feeling is they won't be too many –  Pëkka Feb 14 '11 at 21:32
    
@Pekka: It will catch anyone talking about protocols, hostnames, DNS, URI schemes, and other fields. –  SLaks Feb 14 '11 at 21:36
5  
Sounds like a modwork question. –  Adam Davis Feb 14 '11 at 21:38
1  
@SLaks yeah, but my feeling is that if they do that in more than 50% of their answers, it is likely that they are spammers. Worth a try at any rate, even if I'm wrong –  Pëkka Feb 14 '11 at 21:38
3  
I think it's okay to have false positives, since a moderator will be looking at each one before a decision is made. –  Bill the Lizard Feb 14 '11 at 21:46
    
Things you can do to narrow the search-- user accounts will be created within the last few months, they will have no questions, they will have no comments, they will have less than twenty answers, their rep will be below 5. –  Won't Feb 14 '11 at 22:14
    
@SLaks: A real "plz-give-me-the-codez" question would be: "Hi, I'm new to Android development. Can u plz help me write the next Angry Birds?" –  Robert Harvey Feb 14 '11 at 22:19
1  
@Robert AAAANDROID! Slowly I turned... step... by step... inch... by inch... –  Won't Feb 14 '11 at 22:43

1 Answer 1

Here is a working version of SLaks query :

-- Spam Accounts
-- Users with > 50% answers with links
SELECT
   *
FROM 
   (SELECT * FROM Users u WHERE Id > 0 AND Id < 100000) u
WHERE
    (SELECT COUNT(*) * 2 
     FROM Posts p 
     WHERE u.Id = p.OwnerUserId AND 
           (CHARINDEX('http://', LOWER(p.Body)) > 0 OR 
            CHARINDEX('www.', LOWER(p.Body)) > 0)) >
    (SELECT COUNT(*) FROM Posts p WHERE u.Id = p.OwnerUserId)

Note : This query takes long to execute so if you want to see any result, you need to select only part of the user.

Funny fact : If we consider this metric Jeff Atwood is considered a spammer.


A couple modifications I've made to this query which have worked pretty good...

  • restrict to last 10k users who have joined the system
  • too lazy/don't care to provide their name
  • less than or equal to 11 rep
  • no questions

I already snagged three spammers off this one.

SELECT u.id as [User Link]
FROM (SELECT top 10000 * FROM Users u order by u.Id DESC) u
WHERE
    (SELECT COUNT(*) * 2 
     FROM Posts p 
     WHERE u.Id = p.OwnerUserId AND 
           (CHARINDEX('http://', LOWER(p.Body)) > 0 OR 
            CHARINDEX('www.', LOWER(p.Body)) > 0)) >
    (SELECT COUNT(*) FROM Posts p WHERE u.Id = p.OwnerUserId) AND 
    u.DisplayName is null AND u.Reputation <= 11 AND
    (select COUNT(*) FROM Posts p where u.Id = p.OwnerUserId AND PostTypeId = 1) = 0
share|improve this answer
4  
Ran the query before reading the last line and I destroyed his account. Good job, there. –  Won't Feb 14 '11 at 22:09
    
Added a couple of refinements, hope you don't mind; adding a new answer would have been kinda ripoff-ey –  Won't Feb 14 '11 at 22:51
    
I suggest an additional modification to ensure that URLs local to SE are not counted. I'm not sure if it's possible to do easily, but you might get fewer false-positives that way. –  Jason Plank Feb 15 '11 at 2:32
1  
@Jason there's a TON of safe urls that show up in this. php.org, apache.org, etc etc... –  Won't Feb 15 '11 at 13:24
    
@Will good point, might not be worth the trouble then. –  Jason Plank Feb 15 '11 at 16:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .