0

Please login or register to vote for this query.

(click on this box to dismiss)

Probably Spams Users

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- Website too many times quoted by fews users
-- Probably Spams Users

select top 500 
    count(ta.pid) as [nQuoted] , 
    ta.url,
    count(*) over (partition by ta.url) as nbQuoters,
    ta.uid as [User Link],
    reputation
from (
  select uid, 
    pid ,
   reputation,
    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
        (  
          body, 
          CHARINDEX('HTTP://', UPPER(body)) + 7, 
          300
        ) as urlBody, posts.id as pid, users.id as uid, users.reputation
      from posts inner join users on posts.owneruserid = users.id
      where UPPER(body) like '%HTTP://%'
      and year(posts.creationdate) = 2011
      and users.reputation < 1000
    ) p
) as ta
group by ta.url, ta.uid, reputation
order by count(ta.pid) / (count(*) over (partition by ta.url)) desc

fork query

created jul 26 11 Scorpi0