###Result Set

Looking at the result set of your query, there are multiple fields which are not useful, either because they are always NULL or will always have the same value because of your query. It just creates noise in the results:

• PostTypeId (always 2)
• AcceptedAnswerId (always null)
• Score (always 0)
• ViewCount, AnswerCount, FavoriteCount, ClosedDate, Title & Tags (Answer posts have neither of those, so always null)

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

###Bugs

You also appear to have a few bugs, likely from a missing JOIN or two:

• OwnerDisplayName is always NULL even though the OwnerUserId is always present. (also it's not particularly useful anyways, since you have u.Id AS [User Link].

• LastEditorDisplayName is always NULL even when a LastEditorUsedId is present. One remark on this, in the vast majority of cases it is a self-edit, so I'm not sure how useful that data really is.

It looks like your JOIN with a subquery and other sub-JOIN is silently selecting a bunch of question-related data for display that you don't need, which would explain all the extra columns mentioned above.

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 returns only the useful data, and 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

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

###Result Set

Looking at the result set of your query, there are multiple fields which are not useful, either because they are always NULL or will always have the same value because of your query. It just creates noise in the results:

• PostTypeId (always 2)
• AcceptedAnswerId (always null)
• Score (always 0)
• ViewCount, AnswerCount, FavoriteCount, ClosedDate, Title & Tags (Answer posts have neither of those, so always null)

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

###Bugs

You also appear to have a few bugs, likely from a missing JOIN or two:

• OwnerDisplayName is always NULL even though the OwnerUserId is always present. (also it's not particularly useful anyways, since you have u.Id AS [User Link].

• LastEditorDisplayName is always NULL even when a LastEditorUsedId is present. One remark on this, in the vast majority of cases it is a self-edit, so I'm not sure how useful that data really is.

It looks like your JOIN with a subquery and other sub-JOIN is silently selecting a bunch of question-related data for display that you don't need, which would explain all the extra columns mentioned above.

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 returns only the useful data, and 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

###Result Set

Looking at the result set of your query, there are multiple fields which are not useful, either because they are always NULL or will always have the same value because of your query. It just creates noise in the results:

• PostTypeId (always 2)
• AcceptedAnswerId (always null)
• Score (always 0)
• ViewCount, AnswerCount, FavoriteCount, ClosedDate, Title & Tags (Answer posts have neither of those, so always null)

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

###Bugs

You also appear to have a few bugs, likely from a missing JOIN or two:

• OwnerDisplayName is always NULL even though the OwnerUserId is always present. (also it's not particularly useful anyways, since you have u.Id AS [User Link].

• LastEditorDisplayName is always NULL even when a LastEditorUsedId is present. One remark on this, in the vast majority of cases it is a self-edit, so I'm not sure how useful that data really is.

It looks like your JOIN with a subquery and other sub-JOIN is silently selecting a bunch of question-related data for display that you don't need, which would explain all the extra columns mentioned above.

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 returns only the useful data, and 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