0

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 - https://meta.stackexchange.com/questions/322197/modify-the-has-1-or-0-comments-requirement-for-removeabandonedquestions-scri

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

WITH
-- Qs eligible for RemoveAbandonedQuestions, except for 'has > 1 comment' rule.
cte_questions AS 
(
 SELECT p.Id,
        p.Score
 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
           OR
           (p.OwnerUserId IS NOT NULL AND p.Score < 1)
           )
       AND p.AnswerCount = 0
       AND NOT EXISTS 
       (
        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],
       q.Score,
       c.CreationDate
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.

fork query

edited feb 12 19

created jan 18 19 Andre Silva