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$
12
  • $\begingroup$ Thanks for pointing this out. It does invite an experiment or two I can think of. $\endgroup$
    – hardmath
    Sep 7, 2015 at 22:53
  • $\begingroup$ So... how many questions were asked on the site, deleted and not deleted? $\endgroup$
    – Asaf Karagila Mod
    Sep 7, 2015 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, 2015 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 Mod
    Sep 7, 2015 at 23:44
  • $\begingroup$ @AsafKaragila Yep, type 2 is answer, as written in my post above. $\endgroup$
    – user147263
    Sep 7, 2015 at 23:45
  • $\begingroup$ Now. How many deleted comments are there? :P $\endgroup$
    – Asaf Karagila Mod
    Sep 8, 2015 at 0:11
  • $\begingroup$ So we can't run a query if how many non deleted questions have self-deleted answers? $\endgroup$
    – Asaf Karagila Mod
    Sep 8, 2015 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, 2015 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 Mod
    Sep 9, 2015 at 5:23
  • $\begingroup$ @AsafKaragila Yes; and it already exists (only for posts that are presently undeleted). $\endgroup$
    – user147263
    Sep 9, 2015 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 Mod
    Sep 9, 2015 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, 2015 at 10:44

1 Answer 1

Reset to default
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$
19
  • $\begingroup$ This is useful, thanks. It's a shame more information isn't easily accessible though. $\endgroup$
    – Matt Samuel
    Sep 7, 2015 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, 2015 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, 2015 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 Mod
    Sep 7, 2015 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, 2015 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, 2015 at 6:43
  • 6
    $\begingroup$ Undeleting without fixing the conditions that lead to the deletion is pointless... $\endgroup$
    – Najib Idrissi
    Sep 8, 2015 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 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 Mod
    Sep 8, 2015 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, 2015 at 16:44

You must log in to answer this question.