Short answers with links by domain

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

select Domain, count(*) from
(select 
  substring(a.body, charindex('<a href="http://',a.body)+16, 
    iif(charindex('/', a.body, charindex('<a href="http://',a.body)+16) < charindex('"', a.body, charindex('<a href="http://',a.body)+16), 
      charindex('/', a.body, charindex('<a href="http://', a.body)+16), 
      charindex('"', a.body, charindex('<a href="http://', a.body)+16))
    -(charindex('<a href="http://',a.body)+16)) 
  as Domain,
  a.Score
from
  Posts a,
  Posts q
where
  a.PostTypeId = 2
  and a.Score <=  ##AnswerScoreMax:int##
  and a.Score >= ##AnswerScoreMin:int##
  and LEN(a.Body) < ##AnswerLength##
  and UPPER(a.body) like '%<A HREF="HTTP://%'
  and a.ParentId = q.id
  and q.ClosedDate is null
  and not exists (
    select 1
    from
      PostHistory ph
    where
      ph.PostId = q.id
      and ph.PostHistoryTypeId = 14
  )
  ) x 
  group by Domain
  order by count(*) desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results