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?

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @UserId int = ##UserId##;

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 = @UserId  -- your id
    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 = 905902  -- Jon's id
    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 [UpvoteWinner]
      ,CASE 
		  WHEN yacc = 1 THEN '*You*'
		  WHEN jacc = 1 THEN 'Jon'
		  ELSE               'neither'
	   END AS [Accepted Answer]
FROM   j
UNION ALL
SELECT NULL     -- AS [Post Link]
	  ,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(yacc) AS varchar)
     + ' : '
     + CAST(sum(jacc) AS varchar)  -- Accepted Answer
 FROM  j
ORDER  BY ParentId

Enter Parameters

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