1

Please login or register to vote for this query.

(click on this box to dismiss)

Pulls a list of posts that are older than [x] month(s) with comment(s) made within [y] week(s) prior to the most recent SEDE update. Default for both months and weeks is 1.

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

DECLARE @SEDEUpdated AS DATETIME = (SELECT TOP 1 LastAccessDate FROM Users ORDER BY LastAccessDate DESC)

SELECT
  COUNT(*) AS [#Recent Comments],
  MIN(
    CONCAT('site://posts/comments/', c.Id, '|', CASE
      WHEN p.PostTypeId = 1
      THEN CONCAT('Q: ', p.Title)
      ELSE CONCAT('A: ', parent.Title)
     END)
  ) AS [First Recent Comment Link],
  MAX(c.CreationDate) AS [Most Recent Comment Date],
  p.CreationDate AS [Post Creation Date],
  p.Score AS [Post Score],
  CASE WHEN p.PostTypeId = 1
    THEN p.Tags
    ELSE parent.Tags
  END AS [Tags]
FROM Comments c
JOIN Posts p ON p.Id = c.PostId
LEFT JOIN Posts parent ON parent.Id = p.ParentId
WHERE c.CreationDate > DATEADD(week, -##weeks:int?1##, @SEDEUpdated)
  AND p.CreationDate < DATEADD(month, -##months:int?1##, @SEDEUpdated)
GROUP BY p.Id, p.PostTypeId, p.Title, parent.Title, p.CreationDate, p.Score, p.Tags, parent.Tags
ORDER BY COUNT(*) DESC, MAX(c.CreationDate)

-- months: Minimum Post Age (Months): 
-- weeks: Maximum Comment Age (Weeks):

fork query

edited jul 26 21

created jul 26 21 zcoop98