3

Please login or register to vote for this query.

(click on this box to dismiss)

Pits one user up against another. Where both users have answered the same question a user has won if they have the accepted answer, or they have more votes where neither have the accepted answer. (FYI Jon Skeet (Id:22656), Marc Gravell (Id:23354))

Q&A for anime and manga fans

-- Answerer Fight!
-- Pits one user up against another. Where both users have answered the same question a user has won if they have the accepted answer, or they have more votes where neither have the accepted answer. (FYI Jon Skeet (Id:22656), Marc Gravell (Id:23354))

SELECT SUM(User1Point) AS User1Wins,
       SUM(User2Point) AS User2Wins,
       SUM(1 - (User1Point + User2Point)) AS Draws,
       COUNT(*) AS TotalMatches
FROM

(
SELECT User1Answers.ParentId,
       CASE WHEN (SELECT AcceptedAnswerId
                  FROM Posts
                  WHERE Id = User1Answers.ParentId) = User1Answers.Id
            THEN
                1
            WHEN (SELECT AcceptedAnswerId
                  FROM Posts
                  WHERE Id = User2Answers.ParentId) = User2Answers.Id
            THEN 0
            WHEN User1Answers.Score > User2Answers.Score
            THEN 1
            ELSE 0
       END AS User1Point,
       CASE WHEN (SELECT AcceptedAnswerId
                  FROM Posts
                  WHERE Id = User2Answers.ParentId) = User2Answers.Id
            THEN
                1
            WHEN (SELECT AcceptedAnswerId
                  FROM Posts
                  WHERE Id = User1Answers.ParentId) = User1Answers.Id
            THEN 0
            WHEN User2Answers.Score > User1Answers.Score
            THEN 1
            ELSE 0
       END AS User2Point


FROM Posts User1Answers INNER JOIN
Posts User2Answers ON User1Answers.ParentId = User2Answers.ParentId
WHERE User1Answers.PostTypeId = 2 AND
User1Answers.CommunityOwnedDate IS NULL AND
User1Answers.ClosedDate IS NULL AND
User1Answers.OwnerUserId = ##User1Id## AND

User2Answers.PostTypeId = 2 AND
User2Answers.CommunityOwnedDate IS NULL AND
User2Answers.ClosedDate IS NULL AND
User2Answers.OwnerUserId = ##User2Id##
) tmp

fork query

edited jan 26 12

created oct 5 11 George Duckett