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

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, 
         Country, 
         RANK() OVER (PARTITION BY Country ORDER BY Reputation DESC) AS Rank, 
         Reputation
  FROM(
    SELECT Reputation,
           Id AS UserId,
          CASE
                WHEN UPPER(Location) LIKE '%AHEMEDABABD%' THEN 'India'
                WHEN UPPER(Location) LIKE '%AHMEDABAD%' THEN 'India'
                WHEN UPPER(Location) LIKE '%AJMER%' THEN 'India'
                WHEN UPPER(Location) LIKE '%BANGALORE%' THEN 'India'
                WHEN UPPER(Location) LIKE '%BARODA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%BHILWARA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%BHOPAL%' THEN 'India'
                WHEN UPPER(Location) LIKE '%BHUBNESHWAR%' THEN 'India'
                WHEN UPPER(Location) LIKE '%CHANDIGARH%' THEN 'India'
                WHEN UPPER(Location) LIKE '%CHANDIGHAR%' THEN 'India'
                WHEN UPPER(Location) LIKE '%CHANDIGHARH%' THEN 'India'
                WHEN UPPER(Location) LIKE '%CHENNAI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%DEHRADUN%' THEN 'India'
                WHEN UPPER(Location) LIKE '%DELHI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%DOMBIVLI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%GANDHINAGAR%' THEN 'India'
                WHEN UPPER(Location) LIKE '%GURGAON%' THEN 'India'
                WHEN UPPER(Location) LIKE '%HYDERABAD%' THEN 'India'
                WHEN UPPER(Location) LIKE '%INDIA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%RAJASTHAN%' THEN 'India'
                WHEN UPPER(Location) LIKE '%JAMKALYANPUR%' THEN 'India'
                WHEN UPPER(Location) LIKE '%KERALA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%LUDHIANA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%MADURAI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%MULUND%' THEN 'India'
                WHEN UPPER(Location) LIKE '%MUMBAI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%NEW DELHI%' THEN 'India'
                WHEN UPPER(Location) LIKE '%NOIDA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%PATNA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%PONDICHERRY%' THEN 'India'
                WHEN UPPER(Location) LIKE '%PUNE%' THEN 'India'
                WHEN UPPER(Location) LIKE '%RAJKOT%' THEN 'India'
                WHEN UPPER(Location) LIKE '%SURAT%' THEN 'India'
                WHEN UPPER(Location) LIKE '%TAMIL NADU%' THEN 'India'
                WHEN UPPER(Location) LIKE '%TRIVANDRUM%' THEN 'India'
                WHEN UPPER(Location) LIKE '%UDAIPUR%' THEN 'India'
                WHEN UPPER(Location) LIKE '%VIJAYAWADA%' THEN 'India'
                WHEN UPPER(Location) LIKE '%VISHAKAPATNAM%' THEN 'India'
                WHEN UPPER(Location) LIKE '%VIZAG%' THEN 'India'
            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