Seek and Destroy: Spam spam URLs spam baked beans and spam

0

Please login or register to vote for this query.

(click on this box to dismiss)

A query to help look for spam--finds short posts containing URLs, and shows the ratio of such posts to total posts, per-user. Lots of these are at least borderline spam, and most of the rest are probably poor quality posts anyway. Whee!

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

select u.id as [User Link], u.reputation as [User Rep],
  p.n as [Total Posts], bad.n as [Dubious Posts],
  cast(bad.n as float) / cast (p.n as float) as [Ratio]
from users u 
  inner join (
    select p.owneruserid as uid, count(p.id) as n 
    from posts p
    group by p.owneruserid
  ) p on p.uid = u.id -- count all posts by user
  inner join (
    select p.owneruserid as uid, count(p.id) as n 
    from posts p
    where len(p.body) < 500           -- short posts
      and p.body like '%http://%'     -- that contain a URL
    group by p.owneruserid
  ) bad on bad.uid = u.id
where u.Reputation < 10 
 and p.n > 2                        -- users with at least a few posts
 and cast(bad.n as float) / 
       cast (p.n as float) > 0.5     -- most of which are dubious
       order by CreationDate DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results