The query (on SEDE)

This query builds upon the previous and includes the suggestions by rofl as well as the now mentioned activity index.

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:

^{This will probably be the last one in the Zombie series.}

Motivation

Zombies! The dread of any barbecue. The grilled brains are sticky, their odour is icky, and they never stop talking about crossdead. But that is not the kind of Zombie we suffer from.

We are looking for easy-to revive question Zombies. Questions, that have at least one 0-score answer but count as /unanswered. A revival just needs a single click in those cases: an upvote. We only need to find those Schroedinger questions and cure them.

The first cure accidentally included closed questions, as did the second. This query remedies this fault and only looks for the Zombies that haven't been thrown into jail. It also includes an activity index (see below).

The goal

The previous questions have shown that the requirements on the query were not really communicated well. This time I'll try to list all requirements for the query:

• it must consist of a subset of /unanswered:
  
  
  
  
  • it must not contain closed questions
  
  
  • it must not contain questions that have an accepted answer
  
  
  • it must not contain questions that have an answer with a positive (>0) score, unless the MaxScore is greater than 0
  
  
  
  


• it must not contain questions that have answers with a score greater than the MaxScore
  


• it must not contain questions that have answers with a score lesser than the MinScore
  


• it should only return questions that have been tagged with ##Tagname##
  


• it should include the last activity date of the question


• it should include the latest activity date the question's answers


• it should include an activity index


• it should order the results by the activity index descending first and then ascending by date.

The activity index is basically the difference between the last question activity and the latest question's answers' activity, e.g.

$$ text{activity_index} = max_{a text{ answers } q} a_{text{LastActivityDate}} - q_{text{LastActivityDate}}. $$

Since any answer activity automatically updates the question's LastActivityDate it is always non-positive. The lower the index, the more likely that a new answer is necessary and the current 0-score answer is insufficient or misses the point.