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, 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 group by state; /*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*/