Questions now existing, by the time they were the longest deleted

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow Meta en español

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

WITH Raw AS (
  SELECT
    PostHistory.PostId,
    PostHistory.Id AS PostHistoryId,
    PostHistory.PostHistoryTypeId,
    RANK() OVER (PARTITION BY PostHistory.PostId ORDER BY PostHistory.Id)
      AS Rank,
    PostHistory.CreationDate
  FROM PostHistory, Posts
  WHERE
    Posts.Id = PostHistory.PostId
    AND
    Posts.PostTypeId = 1
    AND
    PostHistory.PostHistoryTypeId BETWEEN 12 AND 13
),
Raw2 AS (
  SELECT
    Del.PostId,
    SUM(DATEDIFF(s, Del.CreationDate, Undel.CreationDate)) AS Sec
  FROM Raw Del, Raw Undel
  WHERE
    Del.PostId = Undel.PostId
    AND
    Del.PostHistoryTypeId = 12
    AND
    Undel.PostHistoryTypeId = 13
    AND
    Del.Rank = Undel.Rank - 1
    AND
    Del.CreationDate < Undel.CreationDate
  GROUP BY Del.PostId
)
SELECT TOP 1000
  PostId AS [Post Link],
  DATEADD(s, Sec, '1900-01-01 00:00:00') AS DeletedTime
FROM Raw2
ORDER BY Sec DESC, PostId DESC

Enter Parameters

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