# 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
SELECT
ParentId,
Max(Score) as Score,
MAX(LastActivityDate) as LastActivityDate
FROM Posts
WHERE PostTypeId = 2
GROUP BY ParentId
)

SELECT
q.Tags,
q.LastActivityDate as [Last question 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
ON    q.Id = a.ParentId
AND a.Score >= @min_score
AND a.Score <= @max_score
WHERE
q.PostTypeId = 1
AND q.ClosedDate IS NULL
ORDER BY
[Activity Index] DESC,
q.LastActivityDate ASC

-- TagName: Tag name as you would use in your post


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.

• Does it take into account the question score? I ran vba - 0 - 0 and this was the first result, with a positive answer- codereview.stackexchange.com/questions/180309/… Mar 24, 2018 at 22:57
• @Raystafarian the SEDE dataset is cached (see FAQ at the bottom). So the answer was updated. And no, the question's score is not taken into account, as it would still show up on /unanswered.
– Zeta
Mar 24, 2018 at 23:02
• Ah, makes sense. didn't realize votes don't change the activity date Mar 24, 2018 at 23:04

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