Get Kraken (practically abandoned by the original poster)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- not really fair to use getdate() here if the data dump is a few days out of date, so go with the most recent post
declare @today as datetime = (select max(creationdate) from Posts)

  select q.Id as [Post Link], q.Score

from Posts q
left join Users u on q.OwnerUserId = u.id

-- Triaged as Should Be Improved at least n days ago
where exists (select 1 from ReviewTasks t 
  join ReviewTaskResults r on r.Id=t.CompletedByReviewTaskId 
    and ReviewTaskResultTypeId=20 -- Should Be Improved
  where t.PostId=q.Id and ReviewTaskTypeId=10 -- Triage
    and t.CreationDate < @today-##deadline##)


-- score can't be above 0
and Score <= 0

-- not closed
and q.ClosedDate is null

-- not locked
and isnull((select top 1 PostHistoryTypeId from PostHistory
  where PostId=q.Id and PostHistoryTypeId in (14,15,35) order by CreationDate desc),0) not in (14,35)

-- no answers scoring above 0 either
and not exists (select 1 from Posts a where ParentId=q.Id and Score>=1)

-- no accepted answers
and AcceptedAnswerId is null

-- no edits in the past deadline days
and isnull(q.LastEditDate, q.CreationDate) < @today-##deadline##

-- and creator has not been active within X hours of creating the post (nominally 72)
and u.LastAccessDate <= DATEADD(hh, ##hours##, q.creationDate)

order by q.score asc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results