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