ANALYSIS OF ZERO-SCORE Answers - 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 using a week in November as sample

Stack Overflow

Q&A for programmers

-- ANALYSIS OF ZERO-SCORE Answers - grouped into 5% percentiles
-- This query attempts to find correlation between low-view tags and
-- zero-score answers using a week in November as sample

select
  [Tags view count percentile] = right(NTile20*5-5,10) + ' to ' + right(NTile20*5,10) + '%',
  Rate_of_zero_score_answers = STR(100.0*sum(Zs)/Sum(Qs), 10,2),
  Max_viewcount_per_tag = Max(Vs)
from
(
select
  Tag,
  [Post Link] = SamplePost,
  Qs = QuestionCount,
  Vs = ViewCount,
  Zs = ZeroVoteCount,
  [%] = ZeroVoteCountRate,
  ZeroVoteRank = Rank() over (order by ZeroVoteCountRate desc),
  TagViewRank = Rank() over (order by ViewCount desc),
  NTile20 = ntile(20) over (order by ViewCount)
from
(
select
  Tag,
  SamplePost = Max(SamplePost),
  ViewCount = Sum(ViewCount),
  QuestionCount = Sum(QuestionCount),
  ZeroVoteCount= Sum(ZeroVoteCount),
  ZeroVoteCountRate = CAST(100.0*Sum(ZeroVoteCount)/Sum(QuestionCount) 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),
  ViewCount,
  QuestionCount,
  ZeroVoteCount,
  SamplePost
from (
    select q.tags,
        Sum(q.ViewCount) ViewCount,
        Count(*) QuestionCount,
        -- NOTE UNSUNG HERO USES VoteCount=0 not Score=0 (?)
        Count(case when a.Score=0 then 1 end) ZeroVoteCount,
        Max(q.id) as SamplePost
    from (select _year=##year:int##, _month=##month:int##) ym
    inner join posts q on q.posttypeid=1
    inner join posts a on a.id = q.acceptedanswerid
    where 
      -- ANY LONGER THAN A MONTH AND IT *MAY* TIME OUT
          q.CreationDate >= cast(_year*10000+_month*100+1 as char(8))
      and q.CreationDate <  dateadd(m,1,cast(_year*10000+_month*100+1 as char(8)))
    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
Where QuestionCount >=10
) Z
group by ntile20
order by ntile20

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached