Improved StackOverflow Rank and Percentile

2

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

-- Improved StackOverflow Rank and Percentile
-- Original by Cade Roux
-- -- Revised to prevent arithmetic overflow error
-- -- UserRep parameter only includes users with rep greater than given amount
-- -- UserRep parameter must be less than rep of given UserId


-- Check that UserID's reputation >= UserRep                                                                                                                                                                                                
DECLARE @rep int
SELECT @rep=Reputation FROM Users WHERE Id = ##UserId##
IF @rep <= ##UserRep##
  BEGIN 
    RAISERROR(N'User reputation is %i; UserRep should be greater than %i.',
      18, 1, @rep, ##UserRep## )
  END
ELSE
WITH Rankings AS (
  SELECT Id, Ranking = ROW_NUMBER() OVER(ORDER BY Reputation DESC)
  FROM Users
  )
  ,Counts AS (
  SELECT Cnt = COUNT(*)
  FROM Users WHERE Reputation > ##UserRep##
  )
  SELECT Id, Ranking, Cnt AS Total_Users, 100 - ((CAST(Ranking AS decimal(6))/Cnt)
  * 100) AS Percentile
  FROM Rankings, Counts
  WHERE Id = ##UserId##

Enter Parameters

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