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)