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?

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

fork query

edited mar 24 12 Erwin Brandstetter

created jun 13 10 cobbal