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.