2

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 the Url displayed, filter on the reputation users

Q&A for programmers

select top 100
        uid as [User Link]
      , url
      , count(1)
from
(
select  p.uid as uid
     , 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 posts.body not like '%stackoverflow%'
  and posts.body not like '%fiddle%'
  and users.reputation < 1000 -- skip high reputation users
  and year(posts.creationdate) = 2013
) p
) as ssss
group by uid, url
order by count(1) desc

fork query

edited apr 29

created jul 26 11 Scorpi0