Most badges compared to reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

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

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

SELECT TOP 1000
      u.Id
    , (SUM(CASE b.Class
            WHEN @GoldClass   THEN @GoldWeight
            WHEN @SilverClass THEN @SilverWeight
            WHEN @BronzeClass THEN @BronzeWeight
            ELSE 0
          END)/u.Reputation) AS 'Score'
    , SUM(CASE b.Class
            WHEN @GoldClass   THEN @GoldWeight
            WHEN @SilverClass THEN @SilverWeight
            WHEN @BronzeClass THEN @BronzeWeight
            ELSE 0
          END) AS 'Summed points'
    , SUM(CASE b.Class WHEN @GoldClass   THEN 1 ELSE 0 END) AS 'Gold badges'
    , SUM(CASE b.Class WHEN @SilverClass THEN 1 ELSE 0 END) AS 'Silver badges'
    , SUM(CASE b.Class WHEN @BronzeClass THEN 1 ELSE 0 END) AS 'Bronze badges'
    , SUM(CASE b.Class WHEN @GoldClass   THEN 1 ELSE 0 END) * @GoldWeight   AS 'Gold points'
    , SUM(CASE b.Class WHEN @SilverClass THEN 1 ELSE 0 END) * @SilverWeight AS 'Silver points'
    , SUM(CASE b.Class WHEN @BronzeClass THEN 1 ELSE 0 END) * @BronzeWeight AS 'Bronze points'
FROM Users u
  INNER JOIN Badges b ON u.Id = b.UserId
GROUP BY u.Id, u.Reputation
ORDER BY Score DESC

Enter Parameters

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