Jon Skeet comparison

0

Please login or register to vote for this query.

(click on this box to dismiss)

What questions does Jon Skeet have a higher/lower score on than me?

Software Quality Assurance and Testing Meta

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

Enter Parameters

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