select Tag, Qs = AcceptedCount, Zs = Accepted0VoteCount, ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Literature Meta

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

-- Enter Query Title
-- Enter Query Description
select
  Tag,
  Qs = AcceptedCount,
  Zs = Accepted0VoteCount,
  [%] = Accepted0VoteCountRate,
  ZeroVoteRank = Rank() over (order by Accepted0VoteCountRate desc),
  TagViewRank = Rank() over (order by AcceptedCount desc)
from
(
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
) Y
Order by ZeroVoteRank Desc

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results