Determines where you rank in a given tag as of the last data load
Q&A for serious players and enthusiasts of chess
-- 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(ranking.[Ranking] AS float) / (SELECT MAX(Ranking) FROM ranking)) * 100.0 AS [Top] FROM ranking) SELECT TOP 51 [Ranking], CASE WHEN ([Top] < 1) THEN '<1%' ELSE (STR([Top], 3) + '%') END AS [Top], [User Link], [Total Score] FROM percentile ORDER BY CASE [User Link] WHEN ##UserId## THEN 1 ELSE 0 END DESC, [Total Score] DESC