19
\$\begingroup\$

During Winter Bash 2014, the Naruto hat was awarded to users who gave an answer that got accepted but hasn't received votes for 12 hours. It was a weird thing, really: why would someone ever accept a question but not upvote it? I haven't yet experienced a situation where this would be reasonable.

Accepted answers without upvotes seem to be an anomaly: it happens when the OP doesn't have enough reputation to upvote, and the post gets overlooked by other users in the community. The community might actually want to clean these up.

To find eligible posts I put together this simple SEDE query (latest version, NOT for review):

DECLARE @username as NVarchar(60) = RTRIM(LTRIM(##DisplayName:string? ##));
DECLARE @userId as int = ##UserId:int?-1##;

SELECT TOP 100
  u.id as [User Link],
  p.Id as [Post Link],
  p.CreationDate
FROM Posts p
  JOIN Users u ON p.OwnerUserId = u.Id
  JOIN Posts q ON p.ParentId = q.Id
WHERE 
  (@username = '' OR u.DisplayName = @username)
  AND (@userId = -1 OR u.Id = @userId)
  AND p.Score = 0
  AND p.Id = q.AcceptedAnswerId
ORDER BY p.CreationDate DESC

The query accepts two parameters:

  • Optional display name, to filter by username
  • Optional user id, to filter by user id
  • With default values it will show all eligible posts of the given site
  • It won't make sense to set both display name and user id, but such misuse would be the user's fault

Something I don't like about this is the username parameter. I would have wanted NULL or empty string as default value but couldn't figure out how to do that. Using space does work, but it's not exactly pretty, I'm wondering if there's a better way I'm missing.

I'm interested in any other suggestions to improve this query.

\$\endgroup\$
1
  • 2
    \$\begingroup\$ This query only tests for a net score of 0, which is not the same as "no upvotes". I'm not sure that SEDE exposes enough voting information to test for no upvotes, though. \$\endgroup\$ Jan 13, 2015 at 20:36

2 Answers 2

9
\$\begingroup\$

There is not a whole lot to say, but I found a few small things that could be improved.


Consistency

DECLARE @username as NVarchar(60) = RTRIM(LTRIM(##DisplayName:string? ##));
DECLARE @userId as int = ##UserId:int?-1##;
DECLARE @limit as int = ##Limit:int?100##;

Would be better as:

DECLARE @userName AS NVARCHAR(60) = RTRIM(LTRIM(##DisplayName:string? ##));
DECLARE @userId AS INT = ##UserId:int?-1##;
DECLARE @limit AS INT = ##Limit:int?100##;

Aliases

I think your aliases mostly obfuscate the query. It's also recommended to explicitly state the type of join, e.g.:

FROM Posts AS posts
  INNER JOIN Users AS users ON p.OwnerUserId = u.Id
  INNER JOIN Posts AS questions ON p.ParentId = q.Id

Trim

Your left and right trim operations don't really achieve anything. Do you expect a user name to have a bunch of white space before or after it? I'm not sure SE would even allow that. I removed them and got identical results.


Everything combined:

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

SELECT TOP 100
  users.Id AS [User Link],
  posts.Id AS [Post Link],
  posts.CreationDate
FROM Posts AS posts
  INNER JOIN Users AS users ON posts.OwnerUserId = users.Id
  INNER JOIN Posts AS questions ON posts.ParentId = questions.Id
WHERE 
  (@username = '' OR users.DisplayName = @username)
  AND (@userId = -1 OR users.Id = @userId)
  AND posts.Score = 0
  AND posts.Id = questions.AcceptedAnswerId
ORDER BY posts.CreationDate DESC

Eliminating the empty string check

There is a way to eliminate the @username = '' check, however it does have a performance impact. I ran it and it runs in ~400 ms, which is not bad but a bit slower. It makes the index scan on the [Users].[UIX_Users_Id] table take longer due to partial string match.

WHERE
  u.DisplayName LIKE CONCAT('%', @username, '%')
  AND (@userId = -1 OR u.Id = @userId)
  AND p.Score = 0
  AND p.Id = q.AcceptedAnswerId
\$\endgroup\$
3
  • \$\begingroup\$ Thanks Phrancis! I'm pretty shocked about the trimming. It would seem SE trims it automatically, otherwise the condition on the DisplayName shouldn't match anything. But it does, which is great. As for the INNER JOIN, it's the default in all RDBMS I've seen, so I think it's unnecessary. \$\endgroup\$
    – janos
    Jan 13, 2015 at 21:07
  • 1
    \$\begingroup\$ That's a good point. I'm trying to think of a way to do away with the '' check, I'll update my answer if I figure anything out \$\endgroup\$
    – Phrancis
    Jan 13, 2015 at 21:16
  • 1
    \$\begingroup\$ I added what I found out, for all its worth. The empty string check may in fact be the lesser of two evils. \$\endgroup\$
    – Phrancis
    Jan 13, 2015 at 22:39
5
\$\begingroup\$

Instead of limiting the results to the first 100 records, it would be better to parameterize, like this:

SELECT TOP ##Limit:int?100##

Selfie accepted answers, while sometimes legitimate, very often they are not. In any case, they don't qualify as Naruto answers, as the lack of upvotes is not due to the inability or negligence of the OP, but quite possibly natural.

Selfie accepted answers are a distinct class of their own, possibly even needing moderator attention, and so it's probably a good idea to exclude these from the query:

JOIN Posts q ON p.ParentId = q.Id AND q.OwnerUserId != u.Id
\$\endgroup\$
4
  • \$\begingroup\$ Just noticed, != should instead be <> they both work (now) but != I think is becoming deprecated. Reference \$\endgroup\$
    – Phrancis
    Mar 24, 2015 at 0:35
  • \$\begingroup\$ Here is some information. The main thing is that <> is the ANSI standard, most DBs still allow != so I guess technically it's not deprecated, just not ANSI-compliant. \$\endgroup\$
    – Phrancis
    Apr 22, 2015 at 18:01
  • \$\begingroup\$ <> is ANSI compliant, while != is not? Say no more, goodbye != then! \$\endgroup\$
    – janos
    Apr 22, 2015 at 18:04
  • \$\begingroup\$ IIRC there's a 48-hour waiting period before you can self-accept, grossly exceeding the 12-hour range needed here. \$\endgroup\$ Feb 17, 2016 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.