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 members of the workforce navigating the professional setting

-- 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) jScore
          ,sum(CASE WHEN q.AcceptedAnswerId = j.Id THEN 1 ELSE 0 END) 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, 22656)  -- Other ID, defaults to Jon
    AND    j.postTypeId  = 2
    GROUP  BY j.ParentId, yScore, yacc
    )
SELECT ParentId AS [Post Link]
	  ,yScore [Your Upvotes]
	  ,jScore [Jon Upvotes]
	  ,CASE
	      WHEN yScore > jScore THEN '*You'
	      WHEN yScore < jScore THEN 'Jon'
	      ELSE                      'tie'
	   END [Upvote Winner]
      ,CASE 
		  WHEN yacc = 1 THEN '*You'
		  WHEN jacc = 1 THEN 'Jon'
		  ELSE               'neither'
	   END [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

fork query

edited mar 24 12 Erwin Brandstetter

created jun 13 10 cobbal