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 for administrators, end users, developers and designers for Craft CMS

-- Jon Skeet comparison (improved)
/*****
How do your answers fare in comparison to Jon Skeet?
Shows your upvotes compared to Jon's, who got more and which one was accepted.

New: Anybody can be Jon Skeet! Enter a User's ID to compare to.
     If none is entered, the match is against Jon Skeet
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
*****/


DECLARE @YourId int = ##UserId##;
DECLARE @JonId 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 = @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, If none, default 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     -- 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

fork query

edited mar 24 12 Erwin Brandstetter

created jun 13 10 cobbal