Questions (from inactive users) with one vote missing to automatic deletion

2

Please login or register to vote for this query.

(click on this box to dismiss)

This query aims to identify borderline questions to the RemoveDeadQuestions script (https://meta.stackexchange.com/help/roomba); i.e., questions older than 30 days, with no answers, not locked, and score equal to zero (one vote missing to -1 which is the last criterium needed for deletion). The query will also filter questions from inactive OPs ('n' days or more), such posts are more likely to be abandoned and/or being of low quality. Tweak (by forking the query) the optional conditions in the WHERE clause to narrow or broaden the search scope. In summary, a downvote will target questions for deletion. An upvote will push them away. If upvoting, try also editing the question for quality/readability. Posting a valuable answer would be even better.

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT p.Id AS [Post Link], -- magic column for post hyperlink (https://data.stackexchange.com/help#parameters).
       p.Score
FROM Posts p
JOIN Users u ON u.Id = p.OwnerUserId
WHERE -- REQUIRED
      p.PostTypeId = 1 AND -- questions.
      p.CreationDate < GETDATE() - 30 AND -- older than 30 days.
      p.Score < 1 AND -- borderline Qs (= 0) + Qs already awaiting roomba (< 0).
      p.AnswerCount = 0
      AND NOT EXISTS 
      (
       SELECT *
       FROM PostHistory ph
       WHERE ph.PostId = p.Id
       AND ph.PostHistoryTypeId = 14 -- not locked.
      ) AND
      -- OPTIONAL
      u.LastAccessDate < GETDATE() - ##days:int?365## -- users inactive for 'n' days or more.
      --days: Users inactive for most recent 'n' days:
      -- AND p.ClosedDate is NULL -- exclude closed posts.
ORDER BY p.Score DESC, -- borderline Qs first.
         p.CreationDate ASC; -- start by the oldest.
         
-- Linked posts: 
-- CV - https://stats.meta.stackexchange.com/questions/5524/why-was-my-question-deleted-if-it-was-not-even-on-hold-nor-closed/5537#5537

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results