0

Please login or register to vote for this query.

(click on this box to dismiss)

Motivated by [this](http://meta.stackoverflow.com/a/251800/794234) Meta.SO question, this attempts to show a change in the quality of questions and answers over time.

Q&A about the site for music historians, critics, and fans

select
  DATEPART(year, a.CreationDate) as [Year],
  count(*) as TotalPosts,
  sum(case when a.Score < -10 then 1 else 0 end) as m11orLess,
  sum(case when a.Score < -7 and a.Score > -11 then 1 else 0 end) as m8tom10,
  sum(case when a.Score < -3 and a.Score > -8 then 1 else 0 end) as m4tom7,
  sum(case when a.Score < 0 and a.Score > -4 then 1 else 0 end) as m1tom3,
  sum(case when a.Score = 0 then 1 else 0 end) as p0,
  sum(case when a.Score > 0 and a.Score < 4 then 1 else 0 end) as p1top3,
  sum(case when a.Score > 3 and a.Score < 8 then 1 else 0 end) as p4top7,
  sum(case when a.Score > 6 and a.Score < 11 then 1 else 0 end) as p7top10,
  sum(case when a.Score > 10 then 1 else 0 end) as p11ormore
 
from
  Posts q
  inner join Posts a on a.ParentId = q.Id

where
  q.PostTypeId = 1 -- question
  and a.PostTypeId = 2 -- answer
  and DATEDIFF(s, q.CreationDate, a.CreationDate) <= ##secondsToAnswer## -- answer w/in 1 min
  and q.OwnerUserId <> a.OwnerUserId -- exclude self answered questions

group by
  DATEPART(year, a.CreationDate)
  
order by
  DATEPART(year, a.CreationDate) desc

fork query

edited apr 23 14 just.another.programmer

created apr 23 14 anonymous