I was writing an exploratory query to see what the average lifespan of questions which are closed as duplicates are then deleted, when I ran into a snag: it doesn't seem like the PostHistory table contains any entries of posts which have been deleted.

Here's the exploratory query that I used:

SELECT Id [Post Link]
FROM PostsWithDeleted pwd
WHERE pwd.Id IN (
  FROM PostHistory ph
  WHERE ph.PostHistoryTypeId = 10
        AND (ph.Comment = '1' OR ph.Comment = '101')
) AND pwd.DeletionDate IS NOT NULL;

I'm well aware that the magic Post Link will break under these circumstances, but it was there due to me fiddling with IS NULL and IS NOT NULL in the database.

Is it the case that I can't go any further as a mere mortal? If so, is there a specific reason that I don't have any further access to the post's history from SEDE?


According to Jon Ericson, the answer is no: "there's no way to see the ClosedDate ... of deleted questions", which would be possible if they were present in PostHistory.

I infer from Jon's posts that the addition of PostsWithDeleted had no effect on other tables. In particular, PostHistory has what it always contained: the history of non-deleted posts. There is no PostHistoryWithDeleted.

  • 1
    Shoot. That's what I had feared. Thanks for finding that. – Makoto Oct 31 '16 at 23:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .