Count of question creation, closure and deletion events by 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

DECLARE @TsNow DATETIME;

SELECT @TsNow = DATEFROMPARTS(
  DATEPART(yyyy, MAX(CreationDate)),
  DATEPART(mm, MAX(CreationDate)),
  1)
FROM Posts;

SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, CreationDate),
    DATEPART(mm, CreationDate),
    1) AS Month,
  'Creation' AS [Type],
  COUNT(*) AS Count
FROM PostsWithDeleted
WHERE
  PostTypeId = 1
  AND
  CreationDate < @TsNow
GROUP BY DATEFROMPARTS(
    DATEPART(yyyy, CreationDate),
    DATEPART(mm, CreationDate),
    1)

UNION ALL

SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, ClosedDate),
    DATEPART(mm, ClosedDate),
    1) AS Month,
  'Closure' AS [Type],
  COUNT(*) AS Count
FROM PostsWithDeleted
WHERE
  PostTypeId = 1
  AND
  ClosedDate < @TsNow
GROUP BY DATEFROMPARTS(
    DATEPART(yyyy, ClosedDate),
    DATEPART(mm, ClosedDate),
    1)

UNION ALL

SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, DeletionDate),
    DATEPART(mm, DeletionDate),
    1) AS Month,
  'Deletion' AS [Type],
  COUNT(*) AS Count
FROM PostsWithDeleted
WHERE
  PostTypeId = 1
  AND
  DeletionDate < @TsNow
GROUP BY DATEFROMPARTS(
    DATEPART(yyyy, DeletionDate),
    DATEPART(mm, DeletionDate),
    1)

ORDER BY
  Month DESC, Count DESC

Enter Parameters

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