6
\$\begingroup\$

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.

\$\endgroup\$
6
\$\begingroup\$

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.
\$\endgroup\$
  • 1
    \$\begingroup\$ I know this is "against rules" but I just want to say this is a terrific answer. Thanks \$\endgroup\$ – IEatBagels Aug 22 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.