HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Forgotten zombie killers

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
forgottenzombiekillers

Problem

Zombies are questions with no upvoted answers.
I came up with this SEDE query to find answers with zero votes on questions that have no accepted answer and no answers with greater than zero score.
In other words, answers on questions that show up on the Unanswered tab,
until the answer receives an upvote. (Latest version is here, FYI, not subject for review.)

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

SELECT TOP 100
  u.Id AS [User Link],
  a.Id AS [Post Link],
  a.CreationDate
FROM Posts a
  JOIN Users u ON a.OwnerUserId = u.Id
  JOIN (
    SELECT q.Id, MAX(a.Score) MaxAnswerScore
    FROM Posts q
    JOIN Posts a ON a.ParentId = q.Id
    WHERE q.AcceptedAnswerId IS NULL
    GROUP BY q.Id
  ) 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.MaxAnswerScore = 0
ORDER BY a.CreationDate DESC


Is there a better way to write this?
Can it be improved?

Solution

This query looks very nice and clean. I can't see any obvious or glaring issues here, I do have some ideas for improvement though:
Naming

a, u, q and p are not all that "expressive" aliases. There is no need to shorten these aliases to be incomprehensible for people outside the SE-System, that don't understand how Posts a is different from Posts q
Select X

How about also making the number of results variable?

SELECT TOP ##topX:int?100##


allows the user to decide how many results they want.
CTE's

Last time I checked, Data Explorer supported CTE's. Make use of them:

WITH Zombies AS (
     SELECT q.Id ZombieId, MAX(a.Score) MaxAnswerScore
     -- ...
)

SELECT ##topX:int?100## FROM Zombies

Code Snippets

SELECT TOP ##topX:int?100##
WITH Zombies AS (
     SELECT q.Id ZombieId, MAX(a.Score) MaxAnswerScore
     -- ...
)

SELECT ##topX:int?100## FROM Zombies

Context

StackExchange Code Review Q#77638, answer score: 5

Revisions (0)

No revisions yet.