Top SO user ranking by a tag.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH ranking AS (SELECT
  RANK() OVER(ORDER BY SUM(answers.score) DESC) AS [Ranking],
  Users.Id AS [User Link],
  SUM(answers.Score) AS [Total Score]
FROM
  Tags
JOIN
  PostTags
ON
  Tags.Id = PostTags.TagId AND Tags.TagName = '##TagName##'
JOIN
  Posts questions
ON
  questions.Id = PostTags.PostId AND questions.CommunityOwnedDate IS NULL
JOIN
  Posts answers
ON
  answers.ParentId = questions.Id AND
  answers.Score > 0 AND
  answers.OwnerUserId IS NOT NULL AND
  answers.CommunityOwnedDate IS NULL
JOIN
 Users
ON
  answers.OwnerUserId = Users.Id
-- WHERE
   -- LOWER(Location) LIKE '%##CountryName##%'    
  
GROUP BY
  Users.Id),
percentile AS (SELECT
  ranking.*,
  (CAST(ranking.[Ranking] AS float) / (SELECT MAX(Ranking) FROM ranking)) * 100.0 AS [Top]
FROM
  ranking)

SELECT TOP 84
  [Ranking],
  CASE WHEN ([Top] < 1) THEN '<1%' ELSE (STR([Top], 3) + '%') END AS [Top],
  [User Link],
  [Total Score]
FROM
  percentile

Enter Parameters

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