1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for designers and implementers of computer programming languages

-- modified from https://codereview.stackexchange.com/questions/226593/zombie-killers-ranking

SELECT TOP ( 25 )
       MAX( Answerers.DisplayName ) [Answerer Name],
       COUNT( * ) [Zombie Kill Count]
  FROM Posts Questions
    INNER JOIN Posts Answers
      ON Questions.Id = Answers.ParentId
    INNER JOIN Users Answerers
      ON Answers.OwnerUserId = Answerers.Id
    OUTER APPLY ( SELECT TOP ( 1 )
                         Id
                    FROM Posts OldAnswers
                    WHERE OldAnswers.ParentId = Questions.Id
                    AND DATEDIFF(d, Questions.CreationDate,OldAnswers.CreationDate) <30
                      AND OldAnswers.Score > 0
                    ORDER BY ( SELECT NULL )) GoodAnswers
  WHERE Questions.PostTypeId = 1
    -- Target old questions with new answers
    AND DATEDIFF(d, Questions.CreationDate,  Answers.CreationDate) >29
    AND Answers.Score > 0
    -- That didn't already have a good answer
    AND GoodAnswers.Id IS NULL
    -- Remove low quality/closed/deleted/community owned questions
    AND Questions.Score >= 0
    AND Questions.ClosedDate IS NULL
    AND Questions.CommunityOwnedDate IS NULL
    AND Questions.DeletionDate IS NULL
  GROUP BY Answerers.Id
  ORDER BY [Zombie Kill Count] DESC;

fork query

edited jan 10 22 Toby.Speight

created sep 8 19 dfhwze