Q&A for scientists using computers to solve scientific problems
WITH QuestionsUA AS ( SELECT DISTINCT Question.Id as Id, 1 as HasUpvotedAnswer FROM PostsWithDeleted as Question, PostsWithDeleted as Answer WHERE Question.Id = Answer.ParentId and Answer.Score > 0 ), Batches AS ( SELECT DATEADD(mm, DATEPART(mm, Question.CreationDate)-1, DATEADD(yy, DATEPART(yyyy, Question.CreationDate)-1900, 0)) as [Date], COUNT(*) as Total, SUM(CASE when Question.Score > 0 then 1 else 0 END) as [Upvoted], SUM(CASE when Question.Score < 0 then 1 else 0 END) as [Downvoted], SUM(CASE when Question.CommentCount > 0 then 1 else 0 END) as [Commented], SUM(CASE when Question.DeletionDate is not null then 1 else 0 END) as [Deleted], SUM(CASE when Question.LastEditDate is not null then 1 else 0 END) as [Edited], SUM(CASE when Question.AnswerCount > 0 then 1 else 0 END) as [Answered], SUM(CASE when Question.AnswerCount = 0 then 1 else 0 END) as [UnAnswered], SUM(CASE when Question.AnswerCount > 0 and Question.AcceptedAnswerId is not null then 1 else 0 END) as [AnsweredAccepted], SUM(CASE when Question.AnswerCount > 0 and Question.AcceptedAnswerId is null then 1 else 0 END) as [AnswerUnAccepted], SUM(CASE when QuestionsUA.HasUpvotedAnswer is not null then 1 else 0 END) as [AnsweredUpvoted] FROM PostsWithDeleted as Question LEFT JOIN QuestionsUA on QuestionsUA.Id = Question.Id WHERE Question.PostTypeId = 1 GROUP BY DATEPART(yyyy, Question.CreationDate), DATEPART(mm, Question.CreationDate) ) SELECT [Date], Commented * 100.0 / Total as [With comment], UnAnswered * 100.0 / Total as [Without answer], AnswerUnAccepted * 100.0 / Total as [Without accepted answer (answered)], AnsweredAccepted * 100.0 / Total as [With accepted answer (answered)] FROM Batches ORDER BY Date;