Deleted by community, undeleted by community - longest time as deleted

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT
    RANK() OVER (PARTITION BY PostId ORDER BY Id) AS N,
    PostId,
    PostHistoryTypeId,
    CreationDate,
    Text
  FROM PostHistory
  WHERE
    PostHistoryTypeId BETWEEN 12 AND 13
)
SELECT TOP 100
  Del.PostId AS [Post Link],
  'site://posts/' + CAST(Del.PostId AS VARCHAR(MAX)) + '/revisions|revisions'
    AS Revisions,
  'site://posts/' + CAST(Del.PostId AS VARCHAR(MAX)) + '/timeline|timeline'
    AS Timeline,
  *
FROM Raw Del, Raw UnDel
WHERE
  Del.PostId = UnDel.PostId
  AND
  Del.N + 1 = UnDel.N
  AND
  Del.PostHistoryTypeId = 12
  AND
  UnDel.PostHistoryTypeId = 13
  AND
  JSON_VALUE(Del.Text, '$.Voters[2].Id') IS NOT NULL
  AND
  JSON_VALUE(UnDel.Text, '$.Voters[2].Id') IS NOT NULL
ORDER BY DATEDIFF(s, Del.CreationDate, UnDel.CreationDate) DESC

Enter Parameters

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