What questions does Jon Skeet have a higher/lower score on than me?
Q&A about the site for members of the workforce navigating the professional setting
-- Jon Skeet comparison (improved) /***** How do your answers fare in comparison to Jon Skeet (or anybody)? Enter NULL to run against Jon Skeet. Or enter anoither UserId. 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 ,yScore ,yacc ,sum(j.Score) AS jScore ,sum(CASE WHEN q.AcceptedAnswerId = j.Id THEN 1 ELSE 0 END) AS 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, yScore, yacc ) SELECT ParentId AS [Post Link] ,yScore AS [Your Upvotes] ,jScore AS [Jons Upvotes] ,CASE WHEN yScore > jScore THEN '*You' WHEN yScore < jScore THEN 'Jon' ELSE 'tie' END AS [Upvote Winner] ,CASE WHEN yacc = 1 THEN '*You' WHEN jacc = 1 THEN 'Jon' ELSE 'neither' END AS [Accepted Answer] FROM j UNION ALL SELECT -1 -- [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 FROM j ORDER BY ParentId