9
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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/… \$\endgroup\$ – Raystafarian Mar 24 '18 at 22:57
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ – Zeta Mar 24 '18 at 23:02
  • \$\begingroup\$ Ah, makes sense. didn't realize votes don't change the activity date \$\endgroup\$ – Raystafarian Mar 24 '18 at 23:04
1
+50
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.