Questions eligible for auto-deletion where comment rule is based on age (instead of n# of comments)


Please login or register to vote for this query.

(click on this box to dismiss)

Questions eligible for RemoveAbandonedQuestions script where comment rule is based on age (instead of n# of comments). Linked posts: MSE -

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

-- Qs eligible for RemoveAbandonedQuestions, except for 'has > 1 comment' rule.
cte_questions AS 
 FROM Posts p
 WHERE p.PostTypeId = 1 -- questions.
       AND p.CreationDate < GETDATE() - 365 -- older than 365 days.
       AND (
           (p.OwnerUserId IS NULL AND p.Score < 2) -- questions with deleted owners
           (p.OwnerUserId IS NOT NULL AND p.Score < 1)
       AND p.AnswerCount = 0
        SELECT *
        FROM PostHistory ph
        WHERE ph.PostId = p.Id
        AND ph.PostHistoryTypeId = 14 -- not locked
       AND p.ViewCount <= DATEDIFF(DAY, p.CreationDate, GETDATE()) * 1.5
-- List of posts and their respective most recent comment.
cte_comments AS
 SELECT c.PostId, 
        MAX(c.CreationDate) AS CreationDate -- most recent comment
 FROM Comments c
 GROUP BY c.PostId
-- Qs eligible for RemoveAbandonedQuestions where most recent comment is older than 'n' months.
SELECT q.Id AS [Post Link],
FROM cte_questions q
JOIN cte_comments c ON c.PostId = q.Id
WHERE c.CreationDate < DATEADD(MONTH, -##months:int?12##, GETDATE()) -- comments older than 'n' months.
-- months: Most recent comment is older than 'n' month(s): 
ORDER BY q.Score DESC, --verify deleted owner rule (where score = 1).
         c.CreationDate DESC; --check date from most recent comment in first Q where score = 0.

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results