# Zombie killers ranking

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
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.

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
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 )
COUNT( * ) [Zombie Kill Count]
FROM Posts Questions
OUTER APPLY ( SELECT TOP ( 1 )
Id
ORDER BY ( SELECT NULL )) GoodAnswers
WHERE Questions.PostTypeId = 1
-- Target old questions with new answers
AND Questions.CreationDate < DATEADD( MONTH, -1, @startDate )
-- 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

• 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.
• The Posts table isn't an actual table but a view over PostsWithDeleted. Its indexes will be used when you select from Posts. The view gets created here – rene Jan 4 at 8:38