Count of question creation, closure and deletion events by month (quicker + smoothable)

1

Please login or register to vote for this query.

(click on this box to dismiss)

It can see only the last closure/deletion events. Reopened/undeleted questions are counted as if they had never been closed/deleted. Both happens rarely (in the overwhelming majority of the cases, only a single closure/deletion happens and no reopen/undelete). TO GET EXACT DATA, SET "Smooth" TO 1!

Stack Apps

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

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM PostsWithDeleted);

SET @now = DATEFROMPARTS(YEAR(@now), MONTH(@now), 1);

CREATE TABLE #tmp (
  Month DATETIME NOT NULL,
  Type INT NOT NULL,
  Count INT NOT NULL,
  PRIMARY KEY(Month, Type)
);

WITH Raw AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Cr,
    DATEFROMPARTS(YEAR(ClosedDate), MONTH(ClosedDate), 1) AS Cl,
    DATEFROMPARTS(YEAR(DeletionDate), MONTH(DeletionDate), 1) AS De
  FROM PostsWithDeleted
  WHERE PostTypeId = ##PostTypeId:int?1##
)
INSERT INTO #tmp
SELECT Cr, 0, COUNT(*) FROM Raw GROUP BY Cr
UNION
SELECT Cl, 1, COUNT(*) FROM Raw WHERE Cl IS NOT NULL GROUP BY Cl
UNION
SELECT De, 2, COUNT(*) FROM Raw WHERE De IS NOT NULL GROUP BY De;

WITH Raw AS (
  SELECT
    Month,
    CASE
      WHEN Type=0 THEN 'Creation'
      WHEN Type=1 THEN 'Closure'
      ELSE 'Deletion'
    END AS Type,
    Count
  FROM #tmp
  WHERE Month < @now
),
Months AS (
  SELECT Month FROM Raw GROUP BY Month
),
Types AS (
  SELECT Type FROM Raw GROUP BY Type
),
Raw2 AS (
  SELECT
    Months.Month,
    Types.Type,
    COALESCE(Raw.Count, 0) AS Count
  FROM Months
  LEFT JOIN Types ON 1=1
  LEFT JOIN Raw ON Months.Month = Raw.Month AND Types.Type = Raw.Type
)
SELECT
  A.Month,
  A.Type,
  1.0
    * SUM(
      (##Smooth## - ABS(DATEDIFF(MONTH, A.Month, B.Month))) * B.Count
    )
    / SUM(##Smooth## - ABS(DATEDIFF(MONTH, A.Month, B.Month)))
    AS Count
FROM Raw2 A, Raw2 B
WHERE
  A.Type = B.Type
  AND
  ABS(DATEDIFF(MONTH, A.Month, B.Month)) < ##Smooth:int?3##
GROUP BY A.Month, A.Type
ORDER BY Month, Type

Enter Parameters

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