0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select PostId,
       count(case when VoteTypeId = 2 then 1 end) 
       - count(case when VoteTypeId = 3 then 1 end) Feedback
into #Anonymous
from PostFeedback
group by PostId;


select ROW_NUMBER() OVER (
         PARTITION BY a.PostTypeId 
         ORDER BY Feedback 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,
       isnull(Feedback, 0)*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
     left join #Anonymous on a.Id = PostId
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

/* -- Anonymous feedback is very sparse, 
   -- so interquartile mean shows all zeros
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;

fork query

edited feb 26 15

created feb 25 15 Jon "not doe" Ericson