Post Creation/Closure/Deletion events per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT 'Created' AS EventType, Id, CreationDate AS TS
  FROM PostsWithDeleted

  UNION

  SELECT 'Closed' AS EventType, Id, ClosedDate AS TS
  FROM PostsWithDeleted
  WHERE ClosedDate IS NOT NULL

  UNION

  SELECT 'Deleted' AS EventType, Id, DeletionDate AS TS
  FROM PostsWithDeleted
  WHERE DeletionDate IS NOT NULL
)
SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, TS),
    DATEPART(m, TS),
    1) AS Month,
  EventType,
  COUNT(*) AS Count
FROM Raw
GROUP BY EventType,
  DATEFROMPARTS(
    DATEPART(yyyy, TS),
    DATEPART(m, TS),
    1)
ORDER BY Month, EventType

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results