0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for scholars and enthusiasts of literature

-- Enter Query Title
-- Enter Query Description
select
  Tag,
  AcceptedCount = Sum(AcceptedCount),
  Accepted0VoteCount = Sum(Accepted0VoteCount),
  Accepted0VoteCountRate = CAST(100.0*Sum(Accepted0VoteCount)/Sum(AcceptedCount) as NUMERIC(10,2))
from
(
select
  Tag = substring(tags, v.number+1, charindex('>', tags, v.number+2)-v.number-1),
  AcceptedCount,
  Accepted0VoteCount
from (
    select q.tags,
        Sum(q.ViewCount) AcceptedCount,
        Count(case when a.Score=0 then 1 end) Accepted0VoteCount
    from posts q
    inner join posts a on a.id = q.acceptedanswerid
    where q.posttypeid=1
      and q.CreationDate >= '20101122' and q.CreationDate < '20101129'
    group by q.tags) q
inner join master..spt_values v on v.type='P'
  and substring(tags, v.number, 1) = '<'
) X
Group by Tag
Order by Accepted0VoteCountRate Desc

fork query

created feb 8 11 cyberkiwi2010