Top badge holders compared to reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Find users with the highest badge score compared to reputation. Badge score is calculated as: Score = gold weight * no. gold badges + silver weight * no. silver badges + bronze weight + no. bronze badges

Physics

Q&A for active researchers, academics and students of physics

-- Params:

DECLARE @MinimumReputation INT = 3000

-- Config:

DECLARE @GoldWeight   INT = 3
DECLARE @SilverWeight INT = 2
DECLARE @BronzeWeight INT = 1

DECLARE @GoldClass   INT = 1
DECLARE @SilverClass INT = 2
DECLARE @BronzeClass INT = 3

-- Query:

SELECT TOP 1000
      u.Id
    , u.Reputation
    , CAST(SUM(
           CASE b.Class
             WHEN @GoldClass   THEN 1
             WHEN @SilverClass THEN 0
             WHEN @BronzeClass THEN 0
             ELSE 0
           END
           )/CAST(u.Reputation AS DECIMAL(10, 2)) AS DECIMAL(5, 5)) AS 'RATIO'
    , SUM(CASE b.Class
            WHEN @GoldClass   THEN @GoldWeight
            WHEN @SilverClass THEN @SilverWeight
            WHEN @BronzeClass THEN @BronzeWeight
            ELSE 0
          END) AS 'Points'
    , SUM(CASE b.Class WHEN @GoldClass   THEN 1 ELSE 0 END) AS 'Gold badges'
FROM Users u
  INNER JOIN Badges b ON u.Id = b.UserId
WHERE u.Reputation <> 1
  AND @MinimumReputation <= u.Reputation
GROUP BY u.Id, u.Reputation
ORDER BY RATIO DESC

Enter Parameters

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