Question Creation/Closure/Deletion events per day

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
  WHERE
    CreationDate
      BETWEEN ##From:string?2000-01-01## AND ##To:string?2100-01-01##
    AND
    PostTypeId = 1

  UNION

  SELECT 'Closed' AS EventType, Id, ClosedDate AS TS
  FROM PostsWithDeleted
  WHERE
    ClosedDate BETWEEN ##From## AND ##To##
    AND
    PostTypeId = 1

  UNION

  SELECT 'Deleted' AS EventType, Id, DeletionDate AS TS
  FROM PostsWithDeleted
  WHERE
    DeletionDate BETWEEN ##From## AND ##To##
    AND
    PostTypeId = 1
)
SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, TS),
    DATEPART(m, TS),
    DATEPART(d, TS)) AS Month,
  EventType,
  COUNT(*) AS Count
FROM Raw
GROUP BY EventType,
  DATEFROMPARTS(
    DATEPART(yyyy, TS),
    DATEPART(m, TS),
    DATEPART(d, TS))
ORDER BY Month, EventType

Enter Parameters

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