0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

WITH 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.AcceptedAnswerId is not null then 1 else 0 END) as [AnsweredAccepted],
  SUM(CASE when AnswerAny.Id is not null then 1 else 0 END) as [AnsweredAny],
  SUM(CASE when AnswerUpvoted.Id is not null then 1 else 0 END) as [AnsweredUpvoted]
FROM
  PostsWithDeleted as Question 
  LEFT JOIN
  PostsWithDeleted as AnswerAny on AnswerAny.ParentId = Question.Id
  LEFT JOIN
  PostsWithDeleted as AnswerUpvoted on AnswerUpvoted.ParentId = Question.Id and AnswerUpvoted.Score > 0
WHERE
  Question.PostTypeId = 1 and
  AnswerAny.PostTypeId = 2 and AnswerUpvoted.PostTypeId = 2
GROUP BY
  DATEPART(yyyy, Question.CreationDate),
  DATEPART(mm, Question.CreationDate)
)
  
SELECT
  [Date],
  Total * 100.0 / Total as [Total],
  Upvoted * 100.0 / Total as [Upvoted],
  Downvoted * 100.0 / Total as [Downvoted],
  Edited * 100.0 / Total as [Edited],
  Deleted * 100.0 / Total as [Deleted],
  Answered * 100.0 / Total as [With any answer],
  AnsweredAny * 100.0 / Total as [With answer],
  AnsweredAccepted * 100.0 / Total as [With accepted answer],
  AnsweredUpvoted * 100.0 / Total as [With upvoted answer]
FROM Batches
ORDER BY Date;

view current version

edited feb 18 20

created feb 17 20 sth29263