1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for apps, scripts, and development with the Stack Exchange API

WITH Raw AS (
  SELECT 'Created' AS EventType, Id, CreationDate AS TS
  FROM PostsWithDeleted
  WHERE PostTypeId = 1

  UNION

  SELECT 'Closed' AS EventType, Id, ClosedDate AS TS
  FROM PostsWithDeleted
  WHERE
    ClosedDate IS NOT NULL
    AND
    PostTypeId = 1

  UNION

  SELECT 'Deleted' AS EventType, Id, DeletionDate AS TS
  FROM PostsWithDeleted
  WHERE
    DeletionDate IS NOT NULL
    AND
    PostTypeId = 1
)
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

fork query

edited aug 19 19

created jun 1 19 peterh (2)