What percentile am I in for a given tag?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Determines where you rank in a given tag as of the last data load

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- What percentile am I in for a given tag?
-- Determines where you rank in a given tag as of the last data load
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
GROUP BY
  Users.Id),
percentile AS (SELECT
  ranking.*,
  cast(ROUND(
    CAST(ROUND(ranking.[Ranking], 6) AS numeric(36,6))
    /
    CAST(ROUND((SELECT MAX(Ranking) FROM ranking), 6) as numeric(36,6))
    * 100.0
    , 6) as numeric(36, 6)
  ) AS [Top]
FROM
  ranking)

SELECT TOP 300
  [Ranking],
  [Top],
  [User Link],
  [Total Score]
FROM
  percentile
ORDER BY
  CASE [User Link] WHEN ##UserId## THEN 1 ELSE 0 END DESC,
  [Total Score] DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results