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, Post.CreationDate)-1, DATEADD(yy, DATEPART(yyyy, Post.CreationDate)-1900, 0)) as [Date],
  COUNT(*) as Total,
  SUM(CASE when Post.Score > 0 then 1 else 0 END) as [Upvoted],
  SUM(CASE when Post.CommentCount > 0 then 1 else 0 END) as [Commented],
  SUM(CASE when Post.FavoriteCount > 0 then 1 else 0 END) as [Favorited],
  SUM(CASE when Post.DeletionDate is not null then 1 else 0 END) as [Deleted],
  SUM(CASE when Post.LastEditorUserId is not null then 1 else 0 END) as [Edited]
FROM
  PostsWithDeleted as Post
WHERE
  Post.PostTypeId = 1
GROUP BY
  DATEPART(yyyy, Post.CreationDate),
  DATEPART(mm, Post.CreationDate)
)
  
SELECT
  [Date],
  Total * 100.0 / Total as [Total],
  Edited * 100.0 / Total as [Edited],
  Upvoted * 100.0 / Total as [Upvoted],
  Deleted * 100.0 / Total as [Deleted],
  Favorited * 100.0 / Total as [Favorited]
FROM Batches
ORDER BY Date;

view current version

edited feb 17 20

created feb 17 20 sth29263