Return to Revisions

###ORDER BY a.CreationDate DESC

I think it would make more sense to sort by ASC so older Zombies show up first. I also think that SELECT TOP 100 is more an obstacle than a help. Since the data only refreshes once a week, if we all wanted to reduce the amount of Zombies we could only do the first 100, then have to wait for refresh. I did notice on SEDE you used SELECT TOP ##Limit:int?100## which is a better approach, but not part of the code in your question.

It looks like your JOIN with a subquery and other sub-JOIN is overly complicating the logic of the query.

I moved some things around to eliminate the unnecessary subquery JOIN and moved the MAX(a.Score) aggregate up into your primary SELECT (notice the HAVING clause at the very end) so this way it simplifies the execution plan and maintainability.

###SEDE Query

DECLARE @username AS NVARCHAR(60) = ##DisplayName:string? ##;
DECLARE @userId AS INT = ##UserId:int?-1##;

SELECT TOP ##Limit:int?100##
  u.Id AS [User Link],
  a.Id AS [Post Link],
  a.CreationDate,
  MAX(a.Score) AS MaxAnswerScore
FROM Posts a
  JOIN Users u 
    ON a.OwnerUserId = u.Id
  JOIN Posts q
    ON a.ParentId = q.Id
WHERE 
  (@username = '' OR u.DisplayName = @username)
  AND (@userId = -1 OR u.Id = @userId)
  AND a.Score = 0
  AND q.AcceptedAnswerId IS NULL
GROUP BY
  u.Id,
  a.Id,
  a.CreationDate
HAVING 
  MAX(a.Score) = 0
ORDER BY a.CreationDate ASC