Finding bad Narutos

Question

While looking for Naruto posts (= overlooked accepted answers well worthy of upvotes), I noticed some odd false positives, selfie answers, where the user who asked the question accepted his own answer under suspicious circumstances.

I'd call suspicious circumstance the case when other answers exist with positive votes, while the selfie has <= 0 score. Quite often these are cases of misuse, where the answer is a simple code dump, rather than an explanation of the improvements, or simply based on suggestions taken (ahem, stolen) from other hard-working reviewers.

I put together this SEDE query (latest):

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

WITH AcceptedAnswers AS (
  SELECT Id, ParentId, Score, OwnerUserId, CreationDate
  FROM Posts
  WHERE PostTypeId = 2
    AND (@userId < 0 OR OwnerUserId = @userId)
    AND (@username = '' OR OwnerUserId IN (
        SELECT Id
        FROM Users
        WHERE DisplayName = @username))
),

SelfAcceptedQuestions AS (
  SELECT q.Id, q.OwnerUserId, q.CreationDate
  FROM Posts q
    JOIN AcceptedAnswers a ON q.Id = a.ParentId
  WHERE q.AcceptedAnswerId IS NOT NULL
    AND q.AcceptedAnswerId = a.Id
    AND q.OwnerUserId = a.OwnerUserId
    AND a.Score <= 0
)

SELECT TOP ##Limit:int?100##
  q.OwnerUserId [User Link],
  q.Id [Post Link],
  q.CreationDate [Post Date]
FROM SelfAcceptedQuestions q
WHERE EXISTS (
    SELECT Id FROM Posts a 
    WHERE q.Id = a.ParentId
      AND a.Score > 0
  )
ORDER by q.CreationDate DESC

What do you think? Any improvement ideas?

Warning: if you run the query and look at the results, keep in mind that not all are bad posts, there are perfectly legitimate exceptions. Either way, the results deserve some attention:

• Bad selfies might be fit to flag for moderator attention, and perhaps some upvotes for the other poor reviewers is due
• Good selfies may actually deserve upvotes

Also keep in mind that the data of SEDE is not up to date, so when you find already flagged or upvoted selfie accepts, it doesn't mean the query is broken.

Answer 1

It looks good to me. Just a minor funky format thing going on here in the very first CTE.

WITH AcceptedAnswers AS (
  SELECT Id, ParentId, Score, OwnerUserId, CreationDate
  FROM Posts
  WHERE PostTypeId = 2
    AND (@userId < 0 OR OwnerUserId = @userId)
    AND (@username = '' OR OwnerUserId IN (
        SELECT Id
        FROM Users
        WHERE DisplayName = @username))

You might want to consider some newlines and extra indentation for that last AND.

WITH AcceptedAnswers AS (
  SELECT Id, ParentId, Score, OwnerUserId, CreationDate
  FROM Posts
  WHERE PostTypeId = 2
    AND (@userId < 0 OR OwnerUserId = @userId)
    AND (@username = '' 
        OR OwnerUserId IN (
            SELECT Id
            FROM Users
            WHERE DisplayName = @username
            )
        )