2

Please login or register to vote for this query.

(click on this box to dismiss)

This query returns questions that have at least one answer with a score of zero. It sorts them by their activity index, which is essentially the time that has passed since the last answer has been posted. The lesser the activity index, the more likely that the Zombie needs a new cure and the current one is not sufficient. This query should only return a subset of questions that are also present in /unanswered.

Q&A about the site for developers and researchers interested in open data

-- TagName: Tag name as you would use in your post
DECLARE @tag_name nvarchar(35) = '##TagName##';

-- MinScore: Highest answer has at least votes:
DECLARE @min_score int = ##MinScore:int?0##;

-- MaxScore: Highest answer has at most votes:
DECLARE @max_score int = ##MaxScore:int?0##;

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(q.LastActivityDate - a.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] ASC ,
  q.LastActivityDate ASC

view current version

edited mar 24 18

created mar 24 18 Zeta