0

Please login or register to vote for this query.

(click on this box to dismiss)

Compare your answers to *any* other user's answers for all questions where you both answered.

Q&A for programmers

-- Enter UserId of match partner or NULL to run against Jon Skeet.

/*****
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
          ,j.CreationDate
          ,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, j.CreationDate, yScore, yacc
    )
SELECT ParentId [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]
       ,CONVERT(varchar(10), CreationDate, 120) AS CreationDate
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
     ,NULL -- [CreationDate]
FROM   j
ORDER  BY ParentId

fork query

edited may 8 12 Erwin Brandstetter

created jun 13 10 cobbal