1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

WITH Raw AS (
  SELECT
    Id,
    PostId,
    PostHistoryTypeId,
    CreationDate,
    LAG(PostHistoryTypeId) OVER (PARTITION BY PostId ORDER BY Id) AS Prev
  FROM PostHistory
  WHERE
    PostHistoryTypeId IN (4,5,6,12,13)
    AND
    PostId IN (
      SELECT PostId
      FROM PostHistory
      WHERE PostHistoryTypeId = 13
    )
),
Raw2 AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY PostId ORDER BY Id) AS Rank
  FROM Raw
  WHERE
    NOT PostHistoryTypeId BETWEEN 4 AND 9
    OR
    NOT Prev BETWEEN 4 AND 9
)
SELECT TOP 100
  Del.PostId AS [Post Link],
  Del.CreationDate AS Deleted,
  Edit.CreationDate AS Edited,
  Undel.CreationDate AS Undeleted
FROM Raw2 Del, Raw2 Edit, Raw2 Undel
WHERE
  Del.PostHistoryTypeId = 12
  AND
  Del.Rank + 1 =  Edit.Rank
  AND
  Del.PostId = Edit.PostId
  AND
  Edit.PostHistoryTypeId BETWEEN 4 AND 9
  AND
  Edit.Rank + 1 = Undel.Rank
  AND
  Edit.PostId = Undel.PostId
  AND
  Undel.PostHistoryTypeId = 13
ORDER BY
  CAST(DATEDIFF(s, Del.CreationDate, Edit.CreationDate) AS BIGINT) *
  DATEDIFF(s, Edit.CreationDate, Undel.CreationDate) DESC

fork query

edited mar 23 20

created mar 23 20 peterh - PLEASE DELETE THIS ACCOUNT