Question Creation/Closure/Deletion events per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

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

Enter Parameters

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