Jon Skeet comparison (improved)

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

-- 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, 905902)  -- 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 NULL        -- [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

Enter Parameters

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