Non-accepted answers overscoring the accepted answers at most

1

Please login or register to vote for this query.

(click on this box to dismiss)

Self-answered questions don't play.

Physics

Q&A for active researchers, academics and students of physics

WITH Raw AS (
  SELECT
    Q.Id AS QId, -- Question Id
    MAX(A.Score) AS MaxNAScore, -- Max score of non-accepted answer
    MAX(Q.AcceptedAnswerId) AS AAId -- Id of accepted answer
  FROM PostsWithDeleted Q, PostsWithDeleted A
  WHERE
    A.ParentId = Q.Id
    AND
    A.Id != Q.AcceptedAnswerId
    AND
    Q.AnswerCount >= 2
    AND
    Q.Id NOT IN (
      SELECT QQ.Id
      FROM Posts QQ, Posts AA
      WHERE
        AA.ParentId = QQ.Id
        AND
        AA.OwnerUserId = QQ.OwnerUserId
    )
  GROUP BY Q.Id
)
SELECT TOP 100
  Raw.QId AS [Post Link],
  Raw.QId, -- Question Id
  Raw.MaxNAScore, -- max score of non-accepted answer
  AA.Score AS AAScore, -- Score of accepted answer
  Raw.MaxNAScore - AA.Score AS Diff -- Difference
FROM Raw, PostsWithDeleted AA
WHERE
  Raw.AAId = AA.Id
ORDER BY Raw.MaxNAScore - AA.Score DESC, Raw.QId

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results