1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

-- Which of my answers received more votes than the accepted answer
 DECLARE @UserId int = ##UserId##;

WITH MyScoreTable AS
  (SELECT Id AS AnswerId,
          ParentId AS QuestionId,
          Score AS MyScore
   FROM Posts
   WHERE OwnerUserId = @UserId
     AND Posts.PostTypeId = 2
     AND Posts.ParentId IN
       (SELECT Posts.Id
        FROM Posts
        WHERE Posts.AcceptedAnswerId IS NOT NULL) ),
     AcceptedAnswerTable AS
  (SELECT Score AS AcceptedScore,
          ParentId AS QuestionId,
          Id AS AnswerId
   FROM Posts
   WHERE Posts.ParentId IN
       (SELECT QuestionId
        FROM MyScoreTable)
     AND Posts.Id IN
       (SELECT DISTINCT AcceptedAnswerId
        FROM Posts) )
SELECT Posts.Id AS [Post Link],
       AcceptedAnswerTable.AcceptedScore,
       MyScoreTable.MyScore,
       Posts.Score AS QuestionScore
FROM Posts
INNER JOIN MyScoreTable ON MyScoreTable.QuestionId = Posts.Id
INNER JOIN AcceptedAnswerTable ON AcceptedAnswerTable.QuestionId = Posts.Id
WHERE MyScoreTable.AnswerId <> AcceptedAnswerTable.AnswerId
  AND MyScore > AcceptedScore
ORDER BY MyScore DESC

fork query

edited sep 27 19

created sep 20 19 d.b