2

Please login or register to vote for this query.

(click on this box to dismiss)

With great power comes great responsibility. See http://stackoverflow.com/help/roomba. The check for reopen votes is usually too conservative, but votes should expire after 14 days at the most. This query excludes questions that are duplicate targets, even though the roomba would be willing to delete them.

Q&A for those who study, teach, research and apply economics and econometrics

--MaxQScore: Max Question Score
declare @qscore int = ##MaxQScore:int?1##
--MaxAScore: Max Answer Score
declare @ascore int = ##MaxAScore:int?1##
declare @now datetime = getdate()

select distinct top ##Top:int?200##
  q.Id as [Post Link],
  q.Score,
  max(a.Score) as [Best Answer],
  q.CreationDate,
  q.ClosedDate,
  q.ViewCount
from Posts q
-- find edits from the past 9 days
left join PostHistory ph on ph.PostId = q.Id
  and datediff(d, ph.CreationDate, @now) <= 9
-- find reopen votes cast in the last 14 days
left join Votes v on v.PostId = q.Id and v.VoteTypeId = '7'
  and datediff(d, v.CreationDate, @now) <= 14
left join Posts a on q.Id = a.ParentId
-- see if question is a duplicate target
left join PostLinks pl on RelatedPostId = q.Id and LinkTypeId = 3
where
  -- question was closed more than 9 days ago
  q.ClosedDate is not null
  and datediff(d, q.ClosedDate, @now) > 9
  -- not closed as a duplicate
  and (select top 1 Comment
   from PostHistory ph
   where ph.PostId = q.Id and PostHistoryTypeId = 10
   order by Id desc) not in (1, 101)
  and q.Score <= @qscore
  -- is not locked
  and coalesce((select top 1 PostHistoryTypeId
    from PostHistory
    where PostHistory.PostId = q.Id
      and PostHistory.PostHistoryTypeId in (14, 15) -- post locked, unlocked
    order by Id desc), 0) <> 14
  and q.AcceptedAnswerId is null
  and (q.Score > 0 or a.Score > 0)
  and q.Tags like '%' + ##Tags:string?%## + '%'
group by q.Id, q.Score, q.CreationDate, q.ClosedDate, q.ViewCount
having coalesce(max(a.Score),0) <= @ascore
  and count(ph.Id) = 0 -- no recent edits
  and count(v.Id) = 0 -- no recent reopen votes
  and count(pl.Id) = 0 -- not a duplicate target
order by max(a.Score), q.Score, CreationDate

select @now as [Time of query]

fork query

edited sep 2 16 amaranth

created jul 20 15 durron597