2

I was curious about the ratio on StackOverflow of posts reopened to posts closed (considering history only, ignoring their current state), and the ratio of posts undeleted to posts deleted. So I figured I'd just count the number of posts associated with each kind of history:

http://data.stackexchange.com/stackoverflow/revision/73611/81883/number-of-posts-with-each-kind-of-history

I got the legend for PostHistoryTypeId from here

Database schema documentation for the public data dump and SEDE

[UPDATE: That posts is from May 2012, so I assumed it was valid, but is it outdated? Looking at the query results again, I see 38 different PostHistoryTypeIds, but only 22 in the legend I got from that post.]

But something appears to be wrong.

According to my query, I get:

Number of Posts Closed    - 91075
Number of Posts Reopened  -  2985

Number of Posts Deleted   - 58518
Number of Posts Undeleted - 58620

The reopen/close ratio seems plausible. But according to this, more posts have been undeleted than posts deleted, which would imply that there have been some posts undeleted without having been deleted in the first place. Seems unlikely. Anyone see where I'm going wrong here?

[UPDATE: @lbstr in the comments points out that these numbers might be aggregates of individual user's votes rather than actions on the post. I interpreted "A post was voted to be closed", for example, to mean "This post garnered enough close votes to be closed", rather than "A close vote was cast on this post". Does it actually mean the latter?]

Improve this question
  • I know that you said you wanted to ingnore their current state, but are you sure you did/can? That is, is the Number of Posts Deleted actually the Number of Posts in a Deleted State? – lbstr Jun 21 '12 at 19:25
  • @lbstr, if I understand correctly, that should return the number of posts that have "Deleted" in their history at any point, so it should't matter what their current state is. – Ben Lee Jun 21 '12 at 19:31
  • Yes, I agree with you there. Is it maybe the fact that more than one user can vote to delete/undelete a given post? – lbstr Jun 21 '12 at 19:35
  • @lbstr, oh, are those numbers for individual votes? I interpreted "Voted to be closed", for example, to mean "garnered enough votes for the post to be closed", not "vote cast for close". Perhaps I misunderstood. I'll update the question asking for clarification. – Ben Lee Jun 21 '12 at 19:41
  • I'm not sure one way or the other. If it was a count of individual votes, then the data would make sense. Hopefully after you update your question, someone can clear that up. – lbstr Jun 21 '12 at 19:43
  • @lbstr, I also just looked at the results and noticed the number of items in the legend (22) does not match the number in the results (38). So maybe the legend is outdated and I'm just looking at the wrong rows. – Ben Lee Jun 21 '12 at 19:46
3

But according to this, more posts have been undeleted than posts deleted, which would imply that there have been some posts undeleted without having been deleted in the first place. Seems unlikely. Anyone see where I'm going wrong here?

Deleted posts don't exist in the data.se because they're not publicly available on the main site. This is the same reason why votes aren't associated with users in the vote table.

So why are there any delete posts at all. Posts that were once deleted and then undeleted will show up in the posthistory. This is why the numbers are so close.

Why are there two more undeleted records? My guess is that the posthistory table didn't always exist or there was a problem at some point in the auditing code.

As a side note you might be interested in this query because it joins to the PostType table which gives the descriptions of the post history types.

it might be aggregates of individual user's votes rather than actions on the post.

Deletion and undeletion votes are like all other votes are recorded in the Votes table (vote typeId's 10 and 11)

Improve this answer

You must log in to answer this question.

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