World Cup of Reputation


Please login or register to vote for this query.

(click on this box to dismiss)

Best average Reputation of Top 100 Users by Country

Stack Overflow

Q&A for professional and enthusiast programmers

-- World Cup of Reputation
-- Best average Reputation of Top 100 Users by Country

WITH UsersCountry AS (
  SELECT UserId, 
         RANK() OVER (PARTITION BY Country ORDER BY Reputation DESC) AS Rank, 
    SELECT Reputation,
           Id AS UserId,
                WHEN UPPER(Location) LIKE '%DENMARK%' THEN 'Denmark'
            ELSE 'Unknown'
         END AS Country
      FROM Users
      WHERE Reputation > 1
      AND LEN(Location) > 2
    ) AS MyTable
TopUsersByCountry AS
  SELECT Country, UserId, Reputation
  FROM UsersCountry
  WHERE Rank = 1
SELECT RANK() OVER (ORDER BY SUM(Users.Reputation)/COUNT(*) DESC) as Rank,
       TopUsers.Country AS Country, 
       COUNT(1) AS NbCountryUsers,   
       SUM(Users.Reputation) AS TotalReputation, 
       SUM(Users.Reputation)/COUNT(*) AS RepsByUser,
       TopUsers.UserId  [User Link], 
       TopUsers.reputation AS LeaderReputation
FROM TopUsersByCountry AS TopUsers
    INNER JOIN UsersCountry AS Users
        ON TopUsers.Country = Users.Country
WHERE 1 = 1
--AND   Users.Rank <= 100 -- Comment to see all Users
GROUP BY TopUsers.Country, 
HAVING COUNT(*) >= 100
ORDER BY SUM(Users.Reputation)/COUNT(*) DESC

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results