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;