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!

Q&A about the site for Drupal developers and administrators

-- Seek and Destroy: Spam spam URLs spam baked beans and spam
-- 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!

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 > 0                        -- users with at least a ONE post, 
                                     -- consistent with the new pattern
 and cast(bad.n as float) / 
       cast (p.n as float) > 0.5     -- most of which are dubious
 and u.reputation < 12               -- user has low rep
order by [Ratio] desc

view current version

edited oct 20 11

created oct 20 11 reemrevnivek