How many questions are reopened after being closed and edited?


Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy Meta

Q&A about the site for science fiction and fantasy enthusiasts

WITH closed_and_edited AS (
SELECT c.PostId, MIN(e.CreationDate) AS EditDate
  FROM PostHistory AS c
  INNER JOIN PostHistory AS e
    ON c.PostId = e.PostId
   AND c.PostHistoryTypeId = 10 -- Post Closed
   AND e.PostHistoryTypeId = 5 -- Edit Body
   AND c.CreationDate < e.CreationDate
  GROUP BY c.PostId
SELECT COUNT(*) AS 'Closed and edited',
  COUNT(oa.PostId) AS 'Closed, edited and reopened',
  ROUND(COUNT(oa.PostId) * 100.0 / COUNT(*), 2) AS '%'
  FROM closed_and_edited AS q
    SELECT PostId
      FROM PostHistory
      WHERE PostId = q.PostId
        AND PostHistoryTypeId = 11 -- Post Reopened
        AND CreationDate >= q.EditDate
  ) oa

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results