Best average Reputation of Top 100 Users by Country
Q&A for Drupal developers and administrators
-- World Cup of Reputation -- Best average Reputation of Top 100 Users by Country WITH UsersCountry AS ( SELECT UserId, Country, RANK() OVER (PARTITION BY Country ORDER BY Reputation DESC) AS Rank, Reputation FROM( SELECT Reputation, Id AS UserId, CASE WHEN UPPER(Location) LIKE '%EYGPT%' THEN 'Egypt' 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, TopUsers.UserId, TopUsers.reputation HAVING COUNT(*) >= 100 ORDER BY SUM(Users.Reputation)/COUNT(*) DESC