Seek and Destroy: Spam Users who are Spamming Urls


Please login or register to vote for this query.

(click on this box to dismiss)

Original query : Here is a version with the Url displayed, filter on the reputation users

Stack Overflow

Q&A for professional and enthusiast programmers

-- Seek and Destroy: Spam spam URLs spam baked beans and spam
-- Original query :
-- Here is an attempt with url

select top 500 as [User Link], 
       u.reputation as [User Rep],
       p.n as [Total Posts], 
       bad.n as [Dubious Posts],
       cast(((100 * bad.n) / p.n) as varchar) + '%' as [Ratio], 
       bad.url as Url
from users u 
  inner join (
    select p.owneruserid as uid, count( as n 
    from posts p
    group by p.owneruserid
  ) p on p.uid = -- count all posts by user
  inner join (
    select ta.uid as uid, 
          count( as n ,
    from (
    select  p.uid as uid, 
   as pid ,
                CASE WHEN CHARINDEX('/', p.urlBody) > CHARINDEX('"', p.urlBody)
                    CHARINDEX('"', p.urlBody)
                    CHARINDEX('/', p.urlBody)
            ) as url
            from (
                select SUBSTRING
                      CHARINDEX('HTTP://', UPPER(posts.body)) + 7, 
                    ) as urlBody, as pid, as uid
                from posts inner join users on posts.owneruserid =
                where UPPER(posts.body) like '%HTTP://%' -- that contain a URL
                and users.reputation < 1000 -- skip high reputation users
            ) p
    ) as ta
    group by ta.uid, ta.url
  ) bad on bad.uid =
where bad.n > 3   -- users with at least 3 dubious post
order by (100*bad.n) / p.n desc, p.n desc, [User Rep] asc

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results