Percentage of (non-deleted) questions which get closed as duplicates, month by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

SELECT
  DATEADD(MONTH, DATEDIFF(MONTH, 0, Posts.CreationDate), 0) AS [MONTH],
  CAST(COUNT(
   CASE WHEN (
         Posts.ClosedDate IS NOT NULL
     AND PostHistory.PostHistoryTypeId = 10
     AND PostHistory.Comment IN ('1', '101')
   ) THEN 1 ELSE NULL END
   ) AS FLOAT) * 100 / COUNT(
     CASE WHEN PostHistory.PostHistoryTypeId = 1 THEN 1 ELSE NULL END
   ) AS 'DupePercentage'
FROM Posts
LEFT JOIN PostHistory ON Posts.Id = PostHistory.PostId
WHERE Posts.PostTypeId = 1
  AND PostHistory.PostHistoryTypeId IN (1, 10)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Posts.CreationDate), 0)
ORDER BY [MONTH] DESC

Enter Parameters

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