What questions does Jon Skeet have a higher/lower score on than me?
Q&A about the site for software quality control experts, automation engineers, and software testers
DECLARE @UserId int = ##UserId##; WITH fights AS ( SELECT y.ParentId ,y.Score AS yScore ,j.Score AS jScore ,CASE WHEN q.AcceptedAnswerId = y.Id THEN 'You' WHEN q.AcceptedAnswerId = j.Id THEN 'Jon' WHEN q.AcceptedAnswerId IS NULL THEN '_open_' ELSE 'other' END AS acc FROM Posts AS j JOIN Posts AS y ON y.ParentId = j.ParentId JOIN Posts AS q ON q.id = j.ParentId WHERE j.OwnerUserId = 905902 AND y.OwnerUserId = @UserId AND j.postTypeId = 2 ) SELECT ParentId AS [Post] ,yScore AS [Your Upvotes] ,jScore AS [Jons Upvotes] ,CASE WHEN yScore > jScore THEN 'You' WHEN yScore < jScore THEN 'Jon' ELSE '_Tie_' END AS [UpvoteWinner] ,acc AS [Accepted Answer] FROM fights UNION ALL SELECT NULL AS [Post] ,sum(yScore) -- AS [Your Upvotes] ,sum(jScore) -- AS [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(CASE WHEN acc = 'You' THEN 1 ELSE 0 END) AS varchar) + ':' + CAST(sum(CASE WHEN acc = 'Jon' THEN 1 ELSE 0 END) AS varchar) -- Accepted Answer FROM fights ORDER BY ParentId