I need to use the Stack Overflow data for some data mining experiments and I have to find out which posts have been deleted.

I have been using the data explorer and trying to see if I could retrieve deleted posts (questions and answers inclusive); however when I try to retrieve posts which have been deleted (i.e posthistorytypeid of 12); I get postids that still point to valid answers on the Stack Overflow page.

For example, the post with the id 508626 has a delete in its history; however it's still showing on this page: How can I include value of sharepoint's version column in a word document?

This is my query:

select postid
from posthistory PP
group by PP.postid
having not exists 
(select * from posthistory where posthistorytypeid = 13 and postid=PP.postid)
and exists (select * from posthistory where posthistorytypeid = 12 and postid=PP.postid)

Deleted content is not included in the data dump or in Data Explorer.

Any post which has a history entry with PostHistoryTypeId = 12 was deleted and then subsequently undeleted at some point, and is therefore visible on the site. Usually this would imply that the post has a later history entry with PostHistoryTypeId = 13, but this isn't always the case for various historical reasons.

You must log in to answer this question.

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