4

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!

Q&A for those who study, teach, research and apply economics and econometrics

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) < 250           -- short posts
      and p.body like '%http://%'     -- that contain a URL
    group by p.owneruserid
  ) bad on bad.uid = u.id
where 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 [Ratio] desc

fork query

created jul 25 11 camccann