7

Please login or register to vote for this query.

(click on this box to dismiss)

Questions tagged with a given tag that may become eligible for auto-deletion

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Questions of a specific tag that tend towards auto-deletion
-- The auto-deletion criterium is: Questions asked over a year ago with score <= 0, no answers, low views proportional to age, one or less comments

SELECT TOP 100
p.Score, p.Id AS [Post Link], p.ViewCount, p.Tags, DATEDIFF(day, p.CreationDate, GETUTCDATE()) as DaysOld
FROM Posts p, PostTags pt, Tags t
WHERE

-- filter by tag
p.Id = pt.PostId
AND t.Id = pt.TagId
AND t.TagName = '##tag##' AND

-- criteria for auto-deleting tags (http://meta.stackexchange.com/q/78048/191131)
--  with a less strict age restriction (usually > 1 year old), and
--  with less strict view count critera (to also see posts that may become eligible for deletion if they are not viewed in ninety days)
--  with a less strict score criteria (to also see posts that may become eligible for deletion if they are down-voted)
p.PostTypeId = 1 AND 
p.ViewCount < (DATEDIFF(day, p.CreationDate, GETUTCDATE()) - 90) * 1.5 AND 
p.Score < 2 AND 
ISNULL(p.AnswerCount,0) = 0 AND
ISNULL(p.CommentCount,0) <= 1 AND
DATEDIFF(day, p.CreationDate, GETUTCDATE()) > 356 - 90
ORDER BY p.Score, p.ViewCount

fork query

edited jun 18 15 oberlies

created feb 7 11 Jeff.Atwood