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
MaxScoreis 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.
