0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

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.AcceptedAnswerId is not null then 1 else 0 END) as [AnsweredAccepted],
  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],
  Total * 100.0 / Total as [Total],
  Upvoted * 100.0 / Total as [Upvoted],
  Downvoted * 100.0 / Total as [Downvoted],
  Commented * 100.0 / Total as [With comment],
  Edited * 100.0 / Total as [Edited],
  Deleted * 100.0 / Total as [Deleted],
  Answered * 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;

fork query

edited nov 27 21 anonymous

created feb 17 20 sth29263