What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 150 Stack Exchange communities.

I like to use Data.SE to view usage stats for some of the sites, however deleted posts are not included on Data.SE and I think this skews the numbers quite a bit, particularly on sites with a lot of deletions.

Would it be possible to include some limited data about deleted posts in Data.SE to make statistical queries more accurate?

You could clear the non-public data fields of deleted posts, such as Posts.Title, Posts.Body, and Posts.OwnerUserId to address privacy concerns explained here. You could even leave the PostId set to 0 since I know SE doesn't want to provide users with a way of searching deleted questions.

The data I would mostly be interested in is CreationDate, LastActivityDate, PostHistory (date closed, date reopened, date deleted, etc), and the fact there was a deleted question in the first place. Up/Down votes, Score, Tags, and View/Answer/Favorite count would also be preferred.

This would make the statistical queries accurate, while still removing the information not meant to be visible to the public.

share|improve this question
I've been meaning to post this for quite a while. I would absolutely love it if this were implemented. –  ben is uǝq backwards Dec 3 '12 at 16:25
"now that Data.SE is automated"...automation or not was never really a motivator for hiding deleted questions, evident by the fact they're consistently hidden everywhere. We simply don't publish a list of deleted content (which you'd have by Id, with this request) anywhere. –  Nick Craver Dec 27 '12 at 11:52
@NickCraver Hrrmm I thought I saw a question like this a while back, and someone declined it because doing a data dump to Data.SE was a manual process and stripping non-public data would be too much of a bother. –  Rachel Dec 27 '12 at 12:32
Hmm, not directly related, but did you ever make a request for keeping successful close/reopen votes in the votes table? I was thinking the other day that the current behaviour is rather useless. –  Tim Stone Dec 31 '12 at 17:02
@TimStone I have now :) –  Rachel Dec 31 '12 at 17:10
@NickCraver - I understand the value of not exposing deleted content as it's generally crud with little to no redeeming value. However, not having access to that information makes it difficult to identify behavioral patterns regarding the genesis of that content. It's hard to suggest quality improvement when I can't definitively say where some of the crud is coming from. –  GlenH7 Apr 17 '13 at 15:10
Can you give some examples of the kind of statistics you're hoping to garner from this? I suspect I know what you're going for, but it's not entirely clear from what you're requesting. –  Shog9 Jun 3 '13 at 19:54
@Shog9 I like to run queries on things like post activity and site usage over a time frame, and new user activity and retention rates. These can be very skewed without deleted posts, especially if the user had a bad start on the site. I've seen other users run statistical queries on Data.SE too, and keep having to remind them that deleted posts are not included. It wasn't a big deal in the past because deleted posts were such a small percentage, but with so many more closed posts now getting deleted with the tweaked auto-delete script, the numbers have shifted quite a bit. –  Rachel Jun 3 '13 at 21:54
@Rachel: actually, the tweaks aren't live yet - they're waiting on a huge pile of other changes that should be going out Real Soon Now. Of course, they will tend to skew things a tad when they do go live, so you may want to investigate whatever oddities you're observing before then. –  Shog9 Jun 5 '13 at 23:25
@Shog9 Hrrmmm I thought the changes were live due to the number of posts deleted by Community on Programmers.SE recently –  Rachel Jun 6 '13 at 3:49
Well, there are two other auto-delete scripts that've been running weekly / monthly for years now... –  Shog9 Jun 6 '13 at 4:26
@Shog9 I was re-running the queries displayed here and noticed a pretty dramatic shift in some of the numbers. Those were posted about a year ago. Around the same time I also noticed the Community user deleting a lot of older posts on Programmers, so thought the script had been updated. It may have just been coincidence though. –  Rachel Jun 10 '13 at 13:38

1 Answer 1

up vote 22 down vote accepted


We've added a new table called PostsWithDeleted that includes metadata from all posts, including the deleted ones. If the post is deleted, we've nulled out all fields except:

  • ID
  • PostTypeId
  • ParentId
  • CreationDate
  • DeletionDate

That's helpful for doing meaningful research that requires looking at activity on the site without being biased by our aggressive deletion scripts.

As of right now on Stack Overflow, that table shows:

questions answers  posts    deleted extant_questions extant_answers 
--------- -------- -------- ------- ---------------- -------------- 
12779084  19666615 32519143 5576247 10133016         16736439       

That lines up with the current stats:

Here's how far we've come together

10,159,316 programming questions

16,766,219 solutions given

Since the data in SEDE is from September 9 (rev 2015.9.9.45), it has a touch fewer posts in it's counts.

We don't include Score as a denormalized field, but you can calculate it by using the Votes table:

select pwd.Id, PostTypeId, ParentId, pwd.CreationDate, DeletionDate,
       sum(iif(VoteTypeId = 2, 1, 0)) - sum(iif(VoteTypeId = 3, -1, 0)) Score
from PostsWithDeleted pwd
     join Votes on pwd.Id = PostId
where pwd.Id = 2
group by pwd.Id, PostTypeId, ParentId, pwd.CreationDate, DeletionDate
order by DeletionDate;

The same technique could be used to count favorites, if that's interesting to you.

At the moment, there's no way to see the ClosedDate, tags or titles of deleted questions. Bodies and the various UserIds are also null for deleted posts, for obvious reasons. We also null out Views, LastEditDate, and LastActivityDate. However, I'm quite pleased with this new table as a way for us to be more transparent about the posts that can't be seen on the site.

share|improve this answer
Oh, so you don't get tags for deleted posts? Hmm, then that cheering was a bit premature. –  Christian Rau Sep 16 at 15:49
Why are votes nulled? That seems it might be useful too? –  enderland Sep 16 at 15:50
@enderland: Well, as I mention in the answer, it's not hard to calculate Score from the Votes table if you are selecting a few posts. We might need to consider leaving Score denormalized if people end up using this table to look at the scores of deleted posts often. –  Jon Ericson Sep 16 at 15:53
@JonEricson I guess that works, thanks whoever made this a priority! It'll be fun I think to play with :D –  enderland Sep 16 at 15:55
@ChristianRau: My initial spec included tags, but that was taken out in the name of caution. –  Jon Ericson Sep 16 at 15:56
I updated the Database schema documentation for the public data dump and SEDE with this new table. –  rene Sep 16 at 16:11
@JonEricson so, tags were considered somehow sensitive, right? would be interesting to learn why, I thought these were safe. I thought tags data could be useful for burnination / blacklisting but since it's unsafe, that's apparently not an option –  gnat Sep 16 at 16:11
It'd be nice to have the deletion user for these, we could analyze when spam hits by looking at thing deleted/locked by Community outside of the normal roomba-ing.s –  Undo Sep 16 at 16:19
@gnat: I sympathize with you. As I said, my initial spec included tags, which I think are both safe and useful. But somewhere in this process we decided to only include the five fields I listed above for deleted posts. That doesn't mean we can't add more fields in later. For the moment, we just want to be sure that we not releasing a djinn who refuses to get back in the jar. –  Jon Ericson Sep 16 at 16:20
I see, you released with least risk possible and keep your options open, that makes good sense indeed –  gnat Sep 16 at 16:22
@Undo You can do this with this data: VoteTypeId = 12 is spam flag. –  Mice Elf Sep 16 at 17:18
@NormalHuman Whoa, I didn't know we included spam flags in the public data dump. Thanks! –  Undo Sep 16 at 17:19
@JonEricson If I remember correctly, votes on deleted posts were not previously included in the Votes table in SEDE. Now they are. Was this change simultaneous with the introduction of PostsWithDeleted, or did I miss something? –  Mice Elf Sep 16 at 17:22
@NormalHuman: I was a bit surprised that votes for deleted posts existed, but I don't think they were added as a part of this change. From what I can tell looking at the revision history, votes on deleted post have been available since the beginning. (Don't hold me to that, however. I'm a bit rusty reading other people's code. ;) –  Jon Ericson Sep 16 at 17:37
Thanks so much!!! Now to find a weekend to brush up on my SQL skills to run some data analysis.... :D –  Rachel Sep 16 at 18:36

You must log in to answer this question.

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