Interquartile mean answer score for closed questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select ROW_NUMBER() OVER (
         PARTITION BY a.PostTypeID 
         ORDER BY a.Score ASC) row,
       case 
            when q.ClosedDate is not null then 'closed'
            else 'open'
       end state,
       case 
            when datediff(month, q.CreationDate, q.ClosedDate) < 1 
                 then 'early close'
            when datediff(month, q.CreationDate, q.ClosedDate) >= 1 
                 then 'late close'
            when q.ClosedDate is null then 'open'
       end close_time,
       a.Score*1.0 ascore,
       q.CreationDate asked,
       q.ClosedDate closed
into #answers
from Posts q
     join Posts a ON a.ParentId = q.Id 
                     and a.OwnerUserId <> q.OwnerUserId
where q.PostTypeId = 1;

select count(*) N,
       state,
       round(avg(ascore), 3) 'average Score',
       round(100.0*count(case 
                             when ascore > 0 then 1 
                           end)/count(*), 1) 'Score > 0',
       round(100.0*count(case 
                             when ascore < 0 then 1 
                           end)/count(*), 1) 'Score < 0',
       round(100.0*count(case 
                             when ascore = 0 then 1 
                           end)/count(*), 1) 'Score = 0'
from #answers
where row between (select count(*)/4 from #answers) 
      and (select 3*count(*)/4 from #answers)
group by state;

select count(*) N,
       close_time,
       round(avg(ascore), 3) 'average Score',
       round(100.0*count(case 
                             when ascore > 0 then 1 
                           end)/count(*), 1) 'Score > 0',
       round(100.0*count(case 
                             when ascore < 0 then 1 
                           end)/count(*), 1) 'Score < 0',
       round(100.0*count(case 
                             when ascore = 0 then 1 
                           end)/count(*), 1) 'Score = 0'
from #answers
where row between (select count(*)/4 from #answers) 
      and (select 3*count(*)/4 from #answers)
group by close_time;

Enter Parameters

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