1

Please login or register to vote for this query.

(click on this box to dismiss)

A query to help look for spam--short, low-score, late answers from users who haven't participated on the site much. Most of these are clearly legit, but some sketchy stuff too.

Q&A about the site for people looking to improve their interpersonal communication skills

-- Seek and Destroy: Spam egg bacon URLs and spam
-- A query to help look for spam--short, low-score, late answers from users who haven't participated on the site much. Most of these are clearly legit, but some sketchy stuff too.

select a.id as [Post Link], a.score as [Answer Score], a.body as [Answer Text], 
  u.reputation as [Answerer Current Rep], ups.postcount as [Answerer Post Count],
  q.creationDate as [Question Asked], a.creationdate as [Last Posted]
from posts a
  inner join posts q on a.parentid = q.id
  inner join users u on a.owneruserid = u.id
  inner join (
    select p.owneruserid, count(p.id) as postcount
    from posts p
    group by p.owneruserid
  ) ups on ups.owneruserid = u.id
where datediff(month, q.creationdate, a.creationdate) > 1 -- late answers
  and len(a.body) < 150                                   -- that are fairly short
  and a.score < 2                                         -- and probably weren't great
  and u.reputation < 200                                  -- from low-rep users
  and u.upvotes + u.downvotes < 20                        -- who don't vote much
  and ups.postcount > 1                                   -- but posted more than once
  and replace(replace(replace(replace(a.body              -- filter out...
    , 'http://msdn.microsoft.com/', '')                   --   MSDN
    , 'http://code.google.com/', '')                      --   Google code
    , 'http://stackoverflow.com/', '')                    --   internal links
    , 'http://en.wikipedia.org/', '')                     --   Wikipedia
    like '%http://%'                                      -- ... and check for other URLs

fork query

created jul 25 11 camccann