Zombie killers ranking

Question

Code Review has a Zombie Problem and, in comparison to The Walking Dead TV Show, our survival doesn't depend on killing them all day every day.

As a potential solution, I though it would be interesting to setup a friendly zombie killing competition (this wasn't talked about in meta yet), but in order for this to work we need a way to rank our killers.

I've written the following Stack Exchange Data Explorer query, which has the following requirements.

Questions must :

• Not be closed/deleted/community owned
• Be at least one month older than the competition's start date
• Not have answers with a score greater than one preceding the competition's start date

And in order for the zombie to be killed, the questions targeted by the above requirements must have an answer with a score of 1 or more that follows the beginning of the competition's date.

I then group the answers per user, show their "kill count" and order by this value, in descending order.

The query in question

declare @startDate date;
set @startDate = CONVERT(datetime, '2019-07-21');

select top(25) u.DisplayName, count(*)
from   Posts q
       inner join Posts a on q.Id = a.ParentId
       inner join Users u on a.OwnerUserId = u.Id
where  q.PostTypeId = 1
       -- Target old questions with new answers
       and datediff(month, q.CreationDate, @startDate) > 1
       and a.Score > 0
       and a.CreationDate > @startDate
       and not exists (select Id from Posts where ParentId = q.Id and CreationDate < @startDate and Score > 0)
       -- Remove low quality/closed/deleted/community owned questions
       and q.Score >= 0
       and q.ClosedDate is null and q.CommunityOwnedDate is null and q.DeletionDate is null
group by u.Id, u.DisplayName
order by count(*) desc

I don't have a database schema in hand, but there are two table concerned :

Posts : Where we have questions and answers. The answers have a ParentId that is a question. A post has an owner (OwnerUserId) with an associated User. I think the rest of the columns names are pretty self explanatory, but I can add details if necessary.

I've tested the query and, to the best of my knowledge, it works. I'm not very used to SEDE so there might be things I missed, which I'd like to learn.

I'd also like to know if there are performance pitfalls in my query I should be aware of or best practices that I'm missing.

The query itself.

Answer 1

As a heads up - we have very different formatting styles for our code. Feel free to ignore that difference and don't consider it a comment on your style (unless you prefer mine, in which case please do).

---

Aliases

I really dislike the practice of using short aliases for tables. I've never seen a point, and it always makes it harder for me to understand. You can get easy aliases (e.g. q -> questions or a -> answers) for very few characters.

I also really like when each column of my output has an actual name, so its easier to understand. In this case, I would alias COUNT(*) as [Zombie Kill Count] or something.

Also, you can use the alias in your ORDER BY, which I find conceptually easier to understand.

---

Performance

One thing that jumps out at me is that you're using a function in your WHERE clause. This can cause the cardinality estimator to get really confused (I don't know if SEDE still uses the legacy cardinality estimator; if they do, then this problem is magnified) and you can end up with some less-than-ideal query plans. One way to avoid this is to not include your column in the function. For example, instead of this:

SELECT *
  FROM Posts
  WHERE DATEDIFF( MONTH, CreationDate, @startDate ) > 1;

You could do this

SELECT *
  FROM Posts
  WHERE CreationDate < DATEADD( MONTH, -1, @startDate );

This lets the cardinality estimator use the available statistics on the CreationDate column without confusing it with the function.

If you're grouping by a value that doesn't actually add a new level of granularity (Users.DisplayName doesn't actually change the grouping) it can be more efficient to use an aggregate there; you get the same result, but a cheaper sort.

SELECT MAX( u.DisplayName ) -- I'm cheaper than grouping on me

Playing around with the APPLY operator can be fun as well; I've often seen it perform better than a NOT EXISTS, like so (with a GoodAnswers.Id IS NULL in the WHERE clause to get an exclusive outer apply). Note - this one wasn't tested, so YMMV. APPLY can be pretty situational, but I always have fun writing them.

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

---

Overall, I came up with something like this.

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

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;

---

Some extra notes:

• Unfortunately, I had a hard time finding information on the available indices on SEDE. The best resource I found was this post. It doesn't list any indices on the Posts table, although we do have one on Users.Id. As such there aren't any great suggestions I have for joins here; you're probably doing about as well as you can.
• You have a pretty lengthy WHERE clause going on; that'll likely dilute the cardinality even further, but I don't see a reasonable alternative.