T-Antivirus

2

Please login or register to vote for this query.

(click on this box to dismiss)

This query returns questions that have at least one answer with a score of zero. It sorts them by their activity index, which is essentially the time that has passed since the last answer has been posted. The lesser the activity index, the more likely that the Zombie needs a new cure and the current one is not sufficient. This query should only return a subset of questions that are also present in /unanswered.

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @tag_name nvarchar(35) = '##TagName##';
DECLARE @min_score int = ##MinScore:int?0##;
DECLARE @max_score int = ##MaxScore:int?0##;

-- CTE to find the maximum answer score on a question. Originally suggested by
-- rofl on https://codereview.stackexchange.com/a/189966/21002
WITH BestAnswer AS (
  SELECT
    ParentId,
    Max(Score) as Score,
    MAX(LastActivityDate) as LastActivityDate
  FROM
    Posts
  WHERE
    PostTypeId = 2
  GROUP BY
    ParentId
)

SELECT 
  q.Id AS [Post Link],
  q.Tags,
  q.LastActivityDate as [Last question activity],
  a.LastActivityDate as [Last answer   activity],
  CAST(a.LastActivityDate - q.LastActivityDate as int) as [Activity Index]
FROM 
  Posts q
    INNER JOIN PostTags pt
    ON    q.Id = pt.PostId
    INNER JOIN Tags t
    ON    pt.TagId = t.id
      AND t.TagName = @tag_name
    INNER JOIN BestAnswer a
    ON    q.Id = a.ParentId
      AND a.Score >= @min_score
      AND a.Score <= @max_score
WHERE 
      q.PostTypeId = 1
  AND q.ClosedDate IS NULL
  AND q.AnswerCount > 0
  AND q.AcceptedAnswerId IS NULL
ORDER BY
  [Activity Index] DESC ,
  q.LastActivityDate ASC
  
-- TagName: Tag name as you would use in your post
-- MinScore: Highest answer has at least votes:
-- MaxScore: Highest answer has at most votes:

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results