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 for apps, scripts, and development with the Stack Exchange API
-- Qs eligible for RemoveAbandonedQuestions, except for 'has > 1 comment' rule.
FROM Posts p
WHERE p.PostTypeId = 1 -- questions.
AND p.CreationDate < GETDATE() - 365 -- older than 365 days.
(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
AND NOT EXISTS
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.
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.