0

Please login or register to vote for this query.

(click on this box to dismiss)

Grabs all questions asked by users who no longer exist and selects the ones where the highest voted answer is NOT the accepted answer. I'm sure this query could be optimized.

Q&A for professional and enthusiast programmers

-- Calculate the highest voted answer and accepted answer for every question.
WITH questions AS (
  SELECT
    question.Id,
    question.ViewCount,
    accepted.Score AS AcceptedScore,
    (
      SELECT MAX(Score)
      FROM Posts
      WHERE
        ParentId = question.Id AND
        question.AcceptedAnswerId != Id
   ) AS HighestScore
  FROM Posts question, Posts accepted
  WHERE
    question.AcceptedAnswerId = accepted.Id AND
    question.PostTypeId = 1 AND
    question.OwnerUserId IS NULL
)

-- Select only the questions containing answers voted higher than the accepted answer.
SELECT
  Id AS [Post Link],
  ViewCount AS [View Count],
  AcceptedScore AS [Score of Accepted Answer],
  HighestScore AS [Score of Highest Voted Answer]
FROM questions
WHERE
  HighestScore > AcceptedScore AND
  ViewCount > 1000

fork query

edited mar 3 13

created mar 3 13 George_Edison