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
GROUP BY
  DATEPART(yyyy, Post.CreationDate),
  DATEPART(mm, Post.CreationDate)
)
  
SELECT
  [Date],
  Total,
  Edited,
  Upvoted,
  Deleted
FROM Batches
ORDER BY Date;

view current version

edited feb 17 20

created feb 17 20 sth29263