The seasoned Zombie sniper: a query to find unanswered questions with specific tags 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 &gt;= @min_score
      AND a.Score &lt;= @max_score
WHERE 
      q.PostTypeId = 1
  AND q.ClosedDate IS NULL
  AND q.AnswerCount &gt; 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.
You have made a nice looking query here. I just have to nit-pick a little bit.  


You query the table Posts with a synonym q where I would expect p instead. I know you want to indicate that it is a question but nevertheless I would expect p instead of q.  
Every sql-statement is written in capital-letters but the AS keyword is written in lower-case-letters.


What you didn't take into account is the entering of a tag-synonym which would be  nice to have as well.