How many questions got reopened in the last two months after being improved?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @ReopenedAfterEdit float;
DECLARE @Edited float;

SELECT @ReopenedAfterEdit = COUNT(DISTINCT(Posts.ID))
FROM Posts
JOIN PostHistory editbody ON editbody.PostID = Posts.ID
JOIN PostHistory reopened ON editbody.PostID = reopened.PostID
JOIN PostHistory closed ON editbody.PostID = closed.PostID
WHERE editbody.PostHistoryTypeId = 5
AND reopened.PostHistoryTypeId = 11
AND closed.PostHistoryTypeId = 10
AND editbody.CreationDate > closed.CreationDate
AND editbody.CreationDate < reopened.CreationDate
AND editbody.CreationDate >= DATEADD(MONTH, -2, GETDATE())

SELECT @Edited = COUNT(DISTINCT(Posts.ID))
FROM Posts
JOIN PostHistory editbody ON editbody.PostID = Posts.ID
JOIN PostHistory closed ON editbody.PostID = closed.PostID
WHERE editbody.PostHistoryTypeId = 5
AND closed.PostHistoryTypeId = 10
AND editbody.CreationDate > closed.CreationDate
AND editbody.CreationDate >= DATEADD(MONTH, -2, GETDATE())

SELECT @ReopenedAfterEdit / @Edited * 100

Enter Parameters

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