Undeleted posts after the longest time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Linguistics Meta

Q&A about the site for professional linguists and others with an interest in linguistic research and theory

WITH Raw AS (
  SELECT
    MAX(DelHist.Id) AS DelHistId,
    UndelHist.Id AS UndelHistId,
    MAX(UndelHist.PostId) AS PostId,
    MAX(DelHist.CreationDate) AS DeletionDate,
    MAX(UndelHist.CreationDate) AS UndelDate,
    DATEDIFF(second, MAX(DelHist.CreationDate), MAX(UndelHist.CreationDate))
      AS Seconds
  FROM PostHistory DelHist, PostHistory UndelHist
  WHERE
    UndelHist.PostHistoryTypeId = 13
    AND
    DelHist.PostHistoryTypeId = 12
    AND
    DelHist.PostId = UndelHist.PostId
    AND
    DelHist.Id < UndelHist.Id
  GROUP BY
    UndelHist.Id
)
SELECT TOP 100
  PostId AS [Post Link],
  'site://posts/' + CAST(PostId AS VARCHAR(MAX)) + '/revisions' AS HistoryLink,
  *
FROM Raw
ORDER BY Seconds DESC, PostId

Enter Parameters

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