Compare your answers to *any* other user's answers for all questions where you both answered.
Q&A for professional and enthusiast programmers
-- Enter UserId of match partner or NULL to run against Jon Skeet. /***** Shows your upvotes compared to Jon's, who got more and which one was accepted. New: This version sums up multiple answers per question correctly in a performance optimized query New: Which answer was accepted? New: Total scores summed up in first line Anybody can be Jon Skeet! Supply a differnet JonId in the headerat the top to match against someone else. *****/ DECLARE @YourId int = ##UserId##; DECLARE @JonId int = ##JonId##; WITH y AS( -- Your answer scores summed up per question SELECT y.ParentId ,sum(y.Score) yScore ,sum(CASE WHEN q.AcceptedAnswerId = y.Id THEN 1 ELSE 0 END) yacc FROM Posts y JOIN Posts q ON q.id = y.ParentId WHERE y.OwnerUserId = @YourId AND y.postTypeId = 2 GROUP BY y.ParentId ) , j AS ( -- Jon's answer scores summed up per question SELECT j.ParentId ,j.CreationDate ,yScore ,yacc ,sum(j.Score) jScore ,sum(CASE WHEN q.AcceptedAnswerId = j.Id THEN 1 ELSE 0 END) jacc FROM Posts j -- only where you answered, too JOIN y ON j.ParentId = y.ParentId JOIN Posts q ON q.id = j.ParentId WHERE j.OwnerUserId = COALESCE(@JonId, 22656) -- Other ID, defaults to Jon AND j.postTypeId = 2 GROUP BY j.ParentId, j.CreationDate, yScore, yacc ) SELECT ParentId [Post Link] ,yScore [Your Upvotes] ,jScore [Jon Upvotes] ,CASE WHEN yScore > jScore THEN '*You' WHEN yScore < jScore THEN 'Jon' ELSE 'tie' END [Upvote Winner] ,CASE WHEN yacc = 1 THEN '*You' WHEN jacc = 1 THEN 'Jon' ELSE 'neither' END [Accepted Answer] ,CONVERT(varchar(10), CreationDate, 120) AS CreationDate FROM j UNION ALL SELECT NULL -- [Post Link] ,sum(yScore) -- [Your Upvotes] ,sum(jScore) -- "Jon's Upvotes" ,CAST(sum(CASE WHEN yScore > jScore THEN 1 ELSE 0 END) AS varchar) + ' : ' + CAST(sum(CASE WHEN yScore < jScore THEN 1 ELSE 0 END) AS varchar) -- UpvoteWinner ,CAST(sum(yacc) AS varchar) + ' : ' + CAST(sum(jacc) AS varchar) -- Accepted Answer ,NULL -- [CreationDate] FROM j ORDER BY ParentId