0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows how the chance of getting an answer has fallen over time

Q&A for anime and manga fans

-- Number of unanswered questions per month by calendar month
-- Shows how the chance of getting an answer has fallen over time

select a.month, total, unanswered,
cast(cast(unanswered*1.0/total*100 as decimal(5,1)) as varchar(5)) + '%' as PercentUnanswered
from 
  (select Convert(varchar(6), creationdate, 112) as month,  count(*) as total
   from posts 
   where posttypeid = 1
   group by Convert(varchar(6), creationdate, 112)) a,
  (select Convert(varchar(6), creationdate, 112) as month,  count(*) as unanswered
   from posts 
   where postTypeID = 1
   and id not in (
     select distinct ParentId from posts 
     where PostTypeId = 2)   
  group by Convert(varchar(6), creationdate, 112)) b
where a.month = b.month
order by a.month

fork query

created oct 31 11 Charles