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 '%EGYPT%' THEN 'Egypt' ELSE 'Unknown' END AS Country FROM Users WHERE Reputation > 1 ) AS MyTable ), TopUsersByCountry AS ( SELECT Country, UserId, Reputation FROM UsersCountry ) SELECT * FROM TopUsersByCountry AS TopUsers