Tag: SQL

One-to-one, we have very different formatting styles for our code. Do not hesitate to ignore this difference and not to see a comment on your style (unless you prefer mine, in which case thank you for doing so).

---

Alias

I really do not like the practice of using short pseudonyms for tables. I have never seen anything particular and it makes me more difficult to understand. You can get easy aliases (for example, q -> questions or a -> answers) for very few characters.

I also love it a lot when each column of my output has a real name, so it's easier to understand. In this case, I would like alias COUNT(*) as (Zombie Kill Count) or something.

In addition, 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 use a function in your WHERE clause. This can cause the cardinality estimator to get really confused (I do not know if SEDE still uses the inherited cardinality estimator, if they do, the problem is magnified) and you may end up with 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 that

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

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

If you group by a value that does not actually add a new level of granularity (Users.DisplayName does not really change the grouping), it may be more efficient to use an aggregate at this location; you get the same result, but less expensive.

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

Play with the APPLY operator can be fun too; I have often seen him perform better than a NOT EXISTS, as if (with a GoodAnswers.Id IS NULL in the WHERE clause to obtain an exclusive external application). Note - this one has not been tested, so YMMV. APPLY can be quite 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 arrived 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 additional notes:

• Unfortunately, I had trouble finding information about available resources.
  indices on SEDE. The best resource I found was this post. he
  does not list any index on the Posts table, although we have
  one on Users.Id. As such, there are no good suggestions that I have
  to join here; you're probably doing as well as you can.
• You have a long enough WHERE clause in progress; it'll probably dilute cardinality even further, but I do not see a reasonable
  alternative.