Average answer score on 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 count(*) N,
       case 
            when q.ClosedDate is not null then 'closed'
            else 'open'
       end 'question state',
       round(avg(a.Score*1.0), 3) 'average Score',
       round(100.0*count(case 
                             when a.Score > 0 then 1 
                           end)/count(*), 1) 'Score > 0',
       round(100.0*count(case 
                             when a.Score < 0 then 1 
                           end)/count(*), 1) 'Score < 0',
       round(100.0*count(case 
                             when a.Score = 0 then 1 
                           end)/count(*), 1) 'Score = 0'
from Posts q
     join Posts a ON a.ParentId = q.Id 
                     and a.OwnerUserId <> q.OwnerUserId
where q.PostTypeId = 1
group by case 
            when q.ClosedDate is not null then 'closed'
            else 'open'
          end;
          
select count(*) N,
       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 'question state',
       round(avg(a.Score*1.0), 3) 'average Score',
       round(100.0*count(case 
                             when a.Score > 0 then 1 
                           end)/count(*), 1) 'Score > 0',
       round(100.0*count(case 
                             when a.Score < 0 then 1 
                           end)/count(*), 1) 'Score < 0',
       round(100.0*count(case 
                             when a.Score = 0 then 1 
                           end)/count(*), 1) 'Score = 0'
from Posts q
     join Posts a ON a.ParentId = q.Id 
                     and a.OwnerUserId <> q.OwnerUserId
where q.PostTypeId = 1
group by 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

Enter Parameters

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