World Cup of Reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Best average Reputation of Top 100 Users by Country

Drupal Answers

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results