Dannnno's zombie killer query review

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

DECLARE @startDate date;
SET @startDate = CONVERT( datetime, '2019-07-21' );

-- 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 OldAnswers.CreationDate < @startDate
                      AND OldAnswers.Score > 0
                    ORDER BY ( SELECT NULL )) GoodAnswers
  WHERE Questions.PostTypeId = 1
    -- Target old questions with new answers
    AND Questions.CreationDate < DATEADD( MONTH, -1, @startDate )
    AND Answers.Score > 0
    AND Answers.CreationDate > @startDate
    -- 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;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results