Compare Answers of two Users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Compares upvotes and accepts for two users where both answered the same questions. Total scores summed up in first line. The first UserID is yours - "you" in the result. (defaults to Erwin) The second UserID is your sparring partner - "he" in the result (defaults to Jon).

Stack Overflow

Q&A for professional and enthusiast programmers

-- Enter UserId of match partners.

DECLARE @YouId int = ##UserId:int?939860##;      -- Erwin as default
DECLARE @HeId  int = ##OtherUserId:int?22656##;  -- Jon as default

WITH you AS(                -- Your answer scores summed up per question
    SELECT a.ParentId, q.AcceptedAnswerId
         , sum(a.Score) AS youScore
         , sum(CASE WHEN q.AcceptedAnswerId = a.Id THEN 1 ELSE 0 END) AS youAccept
    FROM   Posts a
    JOIN   Posts q ON q.id = a.ParentId
    WHERE  a.OwnerUserId = @YouId
    AND    a.postTypeId  = 2
    AND    EXISTS (        -- only where he answered as well
       SELECT 1 FROM Posts ah
       WHERE  ah.OwnerUserId = @HeId
       AND    ah.postTypeId  = 2
       AND    ah.ParentId = a.ParentId
       )
    GROUP  BY a.ParentId, q.AcceptedAnswerId
    )
   , he AS (               -- His answer scores summed up per question
    SELECT a.ParentId
         , a.CreationDate
         , y.youScore
         , y.youAccept
         , sum(a.Score) AS heScore
         , sum(CASE WHEN y.AcceptedAnswerId = a.Id THEN 1 ELSE 0 END) AS heAccept
    FROM   you y            -- only where you answered, too
    JOIN   Posts a ON a.ParentId = y.ParentId
    WHERE  a.OwnerUserId = @HeId
    AND    a.postTypeId  = 2
    GROUP  BY a.ParentId, a.CreationDate, y.youScore, y.youAccept
    )
SELECT ParentId AS [Post Link]
     , youScore AS [Your Upvotes]
     , heScore  AS [His Upvotes]
     , CASE
          WHEN youScore > heScore THEN 'YOU'
          WHEN youScore < heScore THEN 'he'
       END AS [Upvote Winner]
     , CASE 
          WHEN youAccept = 1 THEN 'YOU'
          WHEN heAccept  = 1 THEN 'he'
       END AS [Accepted Answer]
     , CONVERT(varchar(10), CreationDate, 120) AS CreationDate
FROM   he

UNION  ALL
SELECT NULL           -- Post Link
     , sum(youScore)  -- Your Upvotes
     , sum(heScore)   -- His  Upvotes
     , CAST(sum(CASE WHEN youScore > heScore THEN 1 ELSE 0 END) AS varchar)
       + ' : '
       + CAST(sum(CASE WHEN youScore < heScore THEN 1 ELSE 0 END) AS varchar) -- UpvoteWinner
     , CAST(sum(youAccept) AS varchar)
       + ' : '
       + CAST(sum(heAccept) AS varchar)  -- Accepted Answer
     , NULL           -- CreationDate
FROM   he
ORDER  BY ParentId;

Enter Parameters

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