13
$\begingroup$

Stack Exchange Data Explorer now includes the table PostsWithDeleted, which, as the name suggests, includes some data on deleted posts. (Meta announcement.) The content of deleted posts is not made available, however. Only the following information is present:

  • Id of the post, from which one can manufacture a link to it by prepending http://math.stackexchange.com/q/ (or, within SEDE, simply by prepending site:// which makes the query site-independent)
  • Type of the post: 1 for questions, 2 for answers.
  • For answers only: ParentId, which is the Id of the parent question
  • CreationDate and DeletionDate (which is null for non-deleted posts)

How to use this new table?

$\endgroup$
  • $\begingroup$ Thanks for pointing this out. It does invite an experiment or two I can think of. $\endgroup$ – hardmath Sep 7 '15 at 22:53
  • $\begingroup$ So... how many questions were asked on the site, deleted and not deleted? $\endgroup$ – Asaf Karagila Sep 7 '15 at 23:15
  • $\begingroup$ 588266 questions, of them 482018 remain. Over the lifetime of the site, 18% of questions have been deleted. $\endgroup$ – user147263 Sep 7 '15 at 23:39
  • $\begingroup$ Assuming type 2 is "answer", there is about a similar difference between answers and non-deleted answers; although here we are talking about 810k and 704k. $\endgroup$ – Asaf Karagila Sep 7 '15 at 23:44
  • $\begingroup$ @AsafKaragila Yep, type 2 is answer, as written in my post above. $\endgroup$ – user147263 Sep 7 '15 at 23:45
  • $\begingroup$ Now. How many deleted comments are there? :P $\endgroup$ – Asaf Karagila Sep 8 '15 at 0:11
  • $\begingroup$ So we can't run a query if how many non deleted questions have self-deleted answers? $\endgroup$ – Asaf Karagila Sep 8 '15 at 23:20
  • $\begingroup$ @AsafKaragila Deletion reason is not recorded. There are 69090 deleted answers to non-deleted questions. You may also enjoy the list of non-deleted questions with most deleted answers $\endgroup$ – user147263 Sep 8 '15 at 23:33
  • $\begingroup$ I assume that by deletion reason you mean how it came to be deleted (i.e., who voted on it). I don't think that I will enjoy that list, I usually cringe when I see questions with many deleted answers. One final question for now, is it possible to detect undeleted posts? (So for example, can we have a query that pulls out anything with more than one delete-undelete cycle?) $\endgroup$ – Asaf Karagila Sep 9 '15 at 5:23
  • $\begingroup$ @AsafKaragila Yes; and it already exists (only for posts that are presently undeleted). $\endgroup$ – user147263 Sep 9 '15 at 5:30
  • $\begingroup$ Right, but is it possible to find posts whose ultimate fate was deletion after more than one cycle? Or is the entire access to deleted posts is link and time of death? $\endgroup$ – Asaf Karagila Sep 9 '15 at 5:41
  • $\begingroup$ @AsafKaragila That information is not in SEDE: post history table has only extant posts. One would need PostHistoryWithDeleted table to be added. $\endgroup$ – user147263 Sep 9 '15 at 10:44
9
$\begingroup$

As a starter, I wrote a few simple queries:

Someone may want to slice this further, e.g., by a combination of CreationDate and DeletionDate, or by the number of answers (although the AnswerCount is not recorded, it can be inferred by joining the table to itself on a.parentId = q.Id), etc.

$\endgroup$
  • $\begingroup$ This is useful, thanks. It's a shame more information isn't easily accessible though. $\endgroup$ – Matt Samuel Sep 7 '15 at 22:22
  • $\begingroup$ If you browse the end of the third list you will find many quality unanswered questions that were automatically deleted after 1 year. These are the sort of questions that might attract new expertise, since new members often browse the unanswered questions list. I was surprised to see so many good questions that got no upvotes. $\endgroup$ – Bill Dubuque Sep 7 '15 at 22:55
  • $\begingroup$ I voted to undelete some of said questions, so you can find them now in the undelete list in the 10K tools. There are probably many more (unquestionably) good questions there that were deleted by Community in that way. $\endgroup$ – Bill Dubuque Sep 7 '15 at 23:01
  • 9
    $\begingroup$ Wouldn't it be great if those good questions were more visible, so they would get their deserved attention? And so they wouldn't have been auto-deleted to begin with? $\endgroup$ – Asaf Karagila Sep 7 '15 at 23:06
  • 5
    $\begingroup$ Wouldn't it be great if SE thought we could decide these matters better than their bots? $\endgroup$ – Bill Dubuque Sep 8 '15 at 1:42
  • 2
    $\begingroup$ @BillDubuque There is also a chatroom dedicated to reopening, closing, undeleting, deleting posts: chat.stackexchange.com/rooms/info/2165/… If you think some post deserves undeletion, perhaps you could mention it there, together with a short explanation why it should be undeleted. This could give more exposure to undele efforts. $\endgroup$ – Martin Sleziak Sep 8 '15 at 6:43
  • 6
    $\begingroup$ Undeleting without fixing the conditions that lead to the deletion is pointless... $\endgroup$ – Najib Idrissi Sep 8 '15 at 7:08
  • 3
    $\begingroup$ @Bill To a large extent you could unilaterally neuter the 1-year script by starting to upvote those questions that are close to being deleted by it. The search is:question created:2014-09-08 answers:0 score:..0 -views:550 should give you a pretty good starting point for finding those (unquestionably) good questions that are set to be deleted by the next run of the script. (An adjustment to this would help you find those +1-scored questions with a deleted owner.) $\endgroup$ – user642796 Sep 8 '15 at 12:27
  • $\begingroup$ @Arthur As could you or anyone else. Much is trivial with hindsight. The point is that the SE bot is doing damage that is not naturally inhibited. $\endgroup$ – Bill Dubuque Sep 8 '15 at 12:31
  • 2
    $\begingroup$ @Najib Non sequitur. One cannot fix the reasons for deletion (no upvotes) until the question is undeleted. Great logic SE! This is just as bad as the prior bug of not being able to undelete the bots destruction (finally now fixed). $\endgroup$ – Bill Dubuque Sep 8 '15 at 12:33
  • 1
    $\begingroup$ By the way, I find it's a bit misleading to call this process a "bot", implying there's some dumb machine making decisions somewhere. It's the SE staff that decided they didn't want to host old questions that received too little attention. But I guess "Wouldn't it be great if SE thought we could decide these matters better than the SE staff" doesn't sound as good... (I'm not even going to dignify that last comment with a response, Bill can ping me when he's tired of invoking magical formulas to make a point.) || @Arthur I thought unilateral actions were the devil?! $\endgroup$ – Najib Idrissi Sep 8 '15 at 12:37
  • 9
    $\begingroup$ @Bill You are the one most vociferously complaining about it. If you feel so strongly about this perhaps you could take some steps against it. Apart from simply complaining, that is. $\endgroup$ – user642796 Sep 8 '15 at 12:37
  • 1
    $\begingroup$ Sigh, instead of helping to fix these problems, the usual suspects simply try to dismiss them. Some things never change... And they wonder why we have few good teachers left and why the site fails miserably to attract new expertise. $\endgroup$ – Bill Dubuque Sep 8 '15 at 12:38
  • 13
    $\begingroup$ I think some of the great teachers left because they couldn't find the good questions you talk about. $\endgroup$ – Asaf Karagila Sep 8 '15 at 23:19
  • 3
    $\begingroup$ @Bill Coming once again across one of your constructive, solution-driven remarks, I would enjoy it if you were to keep track of a list of questions that you found unworthy of deletion but were deleted. The length of this list divided by the duration of collection might bring some facts to this trench war. $\endgroup$ – Lord_Farin Sep 17 '15 at 16:44

You must log in to answer this question.