0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select year(q.CreationDate) year,
       1.0*sum(iif(a.Id is null, 1, 0))/count(distinct q.Id) unanswered_rate,
       --count(distinct q.Id) asked,
       1.0*count(distinct a.Id)/count(distinct q.Id) answer_rate
from PostsWithDeleted q
     left outer join PostsWithDeleted a 
          on a.ParentId = q.Id
          and a.OwnerUserId <> q.OwnerUserId
          
          and a.CreationDate < q.CreationDate + ##window?30##
          and (a.Score > 0 or q.AcceptedAnswerId = a.Id)
where q.Score >= 0
      and q.PostTypeId = 1
      and (q.ClosedDate is null or q.ClosedDate > q.CreationDate + ##window##)
      and (q.DeletionDate is null or q.DeletionDate > q.CreationDate + ##window##)
group by year(q.CreationDate)
order by year(q.CreationDate)

fork query

edited jan 11 19

created jan 10 19 Jon "not doe" Ericson