1

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.

Q&A for Drupal developers and administrators

SELECT p.Id AS [Post Link], -- magic column for post hyperlink (https://data.stackexchange.com/help#parameters).
       p.ViewCount,
       p.CommentCount,
       u.Reputation as [User Reputation],
       p.Score,
       p.CreationDate,
       u.LastAccessDate,
       p.Tags,
       u.Id as [User Link]
     
       
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
      p.ViewCount < ##ViewCount:int?100## AND
      u.Reputation < ##UserReputation:int?5##
      AND NOT EXISTS 
      (
       SELECT *
       FROM PostHistory ph
       WHERE ph.PostId = p.Id
       AND ph.PostHistoryTypeId = 14 -- not locked.
      ) AND
     DATEDIFF(day, p.creationdate, u.LastAccessDate) < 7 AND
     
      p.ClosedDate is NULL AND
     -- p.Tags LIKE '%##Tags##%' AND --Enable it if you want to filter by tags
      
     
      --Tags: Enter the tag you want to filter:
      -- 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.
         u.LastAccessDate 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

fork query

edited jun 25 anonymous

created jan 8 19 Andre Silva