0

Please login or register to vote for this query.

(click on this box to dismiss)

Original query : http://data.stackexchange.com/stackoverflow/s/1638/seek-and-destroy-spam-spam-urls-spam-baked-beans-and-spam Here is a version with a different order

Q&A for programmers

-- Seek and Destroy: Spam Users who are Spamming Urls, Different Ordered Version
-- Original query : http://data.stackexchange.com/stackoverflow/s/1638/seek-and-destroy-spam-spam-urls-spam-baked-beans-and-spam
-- Here is a version with a different order

select top 500 
       u.id 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(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 ta.uid as uid, 
          count(ta.pid) as n ,
          ta.url
    from (
    select  p.uid as uid, 
            p.pid as pid ,
            SUBSTRING
            (  
                p.urlBody, 
                0, 
                CASE WHEN CHARINDEX('/', p.urlBody) > CHARINDEX('"', p.urlBody)
                THEN
                    CHARINDEX('"', p.urlBody)
                ELSE
                    CHARINDEX('/', p.urlBody)
                END                
            ) as url
            from (
                select SUBSTRING
                    (  
                      posts.body, 
                      CHARINDEX('HTTP://', UPPER(posts.body)) + 7, 
                      len(posts.body)
                    ) as urlBody, posts.id as pid, users.id as uid
                from posts inner join users on posts.owneruserid = users.id
                where UPPER(posts.body) like '%HTTP://%' -- that contain a URL
                and users.reputation < 1000 -- skip high reputation users
                and users.id != -1 -- Community
            ) p
    ) as ta
    group by ta.uid, ta.url
  ) bad on bad.uid = u.id
where bad.n > 3   -- users with at least 3 dubious post
order by ((100*bad.n) / p.n) + (bad.n * 3) desc, -- mix order between ratio and numbers of dubious post 
         [User Rep] asc

fork query

created jul 26 11 Scorpi0