INSPECTION OF ZERO-SCORE Answer - grouped into 5% percentiles

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query attempts to find correlation between low-view tags and zero-score answers

Literature Meta

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

-- INSPECTION OF ZERO-SCORE Answer - grouped into 5% percentiles
-- This query attempts to find correlation between low-view tags and
-- zero-score answers
select
  [Tags view count percentile] = right(NTile20*5-5,10) + ' to ' + right(NTile20*5,10) + '%',
  Rate_of_zero_score_answers = 100.0*sum(Zs)/Sum(Qs),
  Max_questions_per_tag = Max(Qs)
from
(
select
  Tag,
  Qs = AcceptedCount, -- Count of questions with accepted answers by tag
  Zs = Accepted0VoteCount, -- sub-count, of only those with 0-score
  [%] = Accepted0VoteCountRate,
  ZeroVoteRank = Rank() over (order by Accepted0VoteCountRate desc),
  TagViewRank = Rank() over (order by AcceptedCount desc),
  NTile20 = ntile(20) over (order by AcceptedCount)
from
(
select
  Tag,
  AcceptedCount = Sum(AcceptedCount),
  Accepted0VoteCount = Sum(Accepted0VoteCount),
  Accepted0VoteCountRate = CAST(100.0*Sum(Accepted0VoteCount)/Sum(AcceptedCount) as NUMERIC(10,2))
from
(
select
  -- EXPRESSION TO SPLIT THE TAGS OUT
  Tag = substring(tags, v.number+1, charindex('>', tags, v.number+2)-v.number-1),
  AcceptedCount,
  Accepted0VoteCount
from (
    select q.tags,
        Sum(q.ViewCount) AcceptedCount,
        -- NOTE UNSUNG HERO USES VoteCount=0 not Score=0 (?)
        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
      -- ANY LONGER THAN A WEEK AND IT MAY TIME OUT
      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
) Z
--where ntile20 in (1,2,19,20)
group by ntile20
order by ntile20

Enter Parameters

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