WITH UsersCountry AS ( select idUser, country, rank() ove...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Christianity

Q&A for committed Christians, experts in Christianity and those interested in learning more

WITH UsersCountry AS (
    select idUser, country, rank() over (partition by country order by reputation desc) as rank, reputation
  from(
  select Reputation,
           id as idUser,
           case 
            when Location like '%Ann Arbor%' then 'United States'
            when Location like '%Argentina%' then 'Argentina'
            when Location like '%Atlanta%' then 'United States'
            when Location like '%Austin%' then 'United States'
            when Location like '%Australia%' then 'Australia'
            when Location like '%Austria%' then 'Austria'
            when Location like '%Baltimore%' then 'United States'
            when Location like '%Belarus%' then 'Belarus'
            when Location like '%Belgium%' then 'Belgium'
            when Location like '%Boston%' then 'United States'
            when Location like '%Boulder%' then 'United States'
            when Location like '%Bulgaria%' then 'Bulgaria'
            when Location like '%Brazil%' then 'Brazil'
            when Location like '%California%' then 'United States'
            when Location like '%Canada%' then 'Canada'
            when Location like '%Chicago%' then 'United States'
            when Location like '%China%' then 'China'
            when Location like '%Columbus%' then 'United States'
            when Location like '%Connecticut%' then 'United States'
            when Location like '%Dallas%' then 'United States'
            when Location like '%Denmark%' then 'Denmark'
            when Location like '%Denver%' then 'United States'
            when Location like '%Egypt%' then 'Egypt'
            when Location like '%Finland%' then 'Finland'
            when Location like '%Florida%' then 'United States'
            when Location like '%France%' then 'France'
            when Location like '%Germany%' then 'Germany'
            when Location like '%Greece%' then 'Greece'
            when Location like '%Hong Kong%' then 'Hong Kong'
            when Location like '%Houston%' then 'United States'
            when Location like '%Hungary%' then 'Hungary'
            when Location like '%Illinois%' then 'United States'
            when Location like '%India%' then 'India'
            when Location like '%Iran%' then 'Iran'
            when Location like '%Ireland%' then 'Ireland'
            when Location like '%Israel%' then 'Israel'
            when Location like '%Italy%' then 'Italy'
            when Location like '%Japan%' then 'Japan'
            when Location like '%Los Angeles%' then 'United States'
            when Location like '%Madison%' then 'United States'
            when Location like '%Massachusetts%' then 'United States'
            when Location like '%Minneapolis%' then 'United States'
            when Location like '%Minnesota%' then 'United States'
            when Location like '%Mountain View%' then 'United States'
            when Location like '%Netherlands%' then 'Netherlands'
            when Location like '%New Jersey%' then 'United States'
            when Location like '%New York%' then 'United States'
            when Location like '%New Zealand%' then 'New Zealand'
            when Location like '%Norway%' then 'Norway'
            when Location like '%Pakistan%' then 'Pakistan'
            when Location like '%Philadelphia%' then 'United States'
            when Location like '%Phoenix%' then 'United States'
            when Location like '%Pittsburgh%' then 'United States'
            when Location like '%Poland%' then 'Poland'
            when Location like '%Portland%' then 'United States'
            when Location like '%Portugal%' then 'Portugal'
            when Location like '%Raleigh%' then 'United States'
            when Location like '%Redmond%' then 'United States'
            when Location like '%Romania%' then 'Romania'
            when Location like '%Russia%' then 'Russia'
            when Location like '%San Diego%' then 'United States'
            when Location like '%San Francisco%' then 'United States'
            when Location like '%Seattle%' then 'United States'
            when Location like '%Serbia%' then 'Serbia'
            when Location like '%Singapore%' then 'Singapore'
            when Location like '%Slovenia%' then 'Slovenia'
            when Location like '%South Africa%' then 'South Africa'
            when Location like '%Spain%' then 'Spain'
            when Location like '%Sweden%' then 'Sweden'
            when Location like '%Switzerland%' then 'Switzerland'
            when Location like '%Texas%' then 'United States'
            when Location like '%Turkey%' then 'Turkey'
            when Location like '%Ukraine%' then 'Ukraine'
            when Location like '%United Kingdom%' then 'United Kingdom'
            when Location like '%United States%' then 'United States'
            when Location like '%Virginia%' then 'United States'
            when Location like '%Washington%' then 'United States'
            else 'Unknown'
           end as Country
      from Users
      where reputation>1
      and location != ''
  ) as t
),
TopUsersByCountry AS
( SELECT country, iduser, reputation
from UsersCountry
where rank = 1)
select idUser AS [User Link], country, reputation from TopUsersByCountry

Enter Parameters

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