How can I write a query DATA.SE to list top users from all countries in the world?

Question

I am trying to create a query, that lists TOP user(s) by reputation from all countries. I don't know where to start from. I have a query, which lists TOP user from a specific country:

SELECT top 1
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
    Id AS [User Link], 
    Reputation 
FROM
    Users 
WHERE
  (
        lower(Location) LIKE ('%greece%')
  )
  and not
  (
    lower(Location) LIKE '%greecer%'
    or 
    lower(Location) LIKE '%greecen%'
  )
    
ORDER BY
    Reputation DESC;

However, I want to have add (join or whatever) a query which will add the top user from 5 other countries, i.e. Croatia, Spain , etc... so I will need a result, where will be only 5 rows.

---

EDIT: according to @rene, I've posted my answer too for excellent solution.

Answer 1

Here is the first attempt at a SEDE query, partly inspired by the query from Martin

What I've done here is creating three support tables first: Countries, Alternative spellings for the country and spellings you want to exclude for a country.

The Alternative spelling table will then be joined with the users table based on the location field and then a not exists clause is used to remove the locations that don't belong that specific country.

The resulting set is then RANKed over the countryid as partition and ordered by the descending reputation within that partition.

Last step is joining with the Countries table and selecting only the user that is ranked first.

-- this temp tables limits the number of joins needed
select id, reputation, location
into #users
from users
where location is not null  -- empty locations can be skipped
and reputation > 1 -- lots of users have 1 rep

;with countries as 
(
   select 1 id, 'greece' name union
   select 2, 'Slovensko' union
   select 3, 'germany'
),
country_writings as 
(
  select 1 countryid, 'athens' altname union
  select 1, 'greece' union
  select 2, 'slovak' union
  select 2, 'banska' union
  select 2, 'martin' union
  select 2, 'komarno' union
  select 3, 'germany' union
  select 3, 'bonn' union 
  select 3, 'hamburg'
),
exclude_writings as (
select 1 countryid, 'greecer' altname union
select 1, 'greecen' union
select 2, 'martins' union
select 2, 'martin, TN, USA' union
select 2, 'kenitra' union
select 3, 'hamburger' 
),
users_and_country as
(
select distinct 
       cw.countryid
     , u.id
     , u.reputation
from #users u
inner join country_writings cw 
   on (   u.location = cw.altname COLLATE Latin1_General_CI_AI  -- exact match first
       or u.location like concat('%', cw.altname, '%') COLLATE Latin1_General_CI_AI
      )
where not exists (
  select *
  from exclude_writings ew
  where ew.countryid = cw.countryid
  and u.location like concat('%', ew.altname, '%') COLLATE Latin1_General_CI_AI
) 
)

select c.name, data.id as [User Link], reputation
from countries c
inner join
(
select rank() over(partition by countryid order by reputation desc) [pos]
     , *
from users_and_country 
) data on c.id = countryid 
      and pos = 1 -- only the top user for a country

When run today on Mathematics this is the result:

A possible alternative to having three supporting tables is storing the alternative and to exclude names in the Countries table, something like this:

with countries as 
(
   select 1 id
        , 'greece' name 
        , 'greece/athens' alt
        , 'greecer/greecen' excl
   union
   -- etc
)

and then use a STRING_SPLIT on the char '/' to get the alternative spellings. This has the downside that if you want to exclude something with a / in it you need to find a different pattern.

I guess that is somewhat cleaner / easier to maintain. I leave it as an exercise for the reader to make all the changes needed for that variant.

Keep in mind SEDE is updated once a week on Sunday.
Give a big shout-out to Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.

Answer 2

[According to @Rene's excellent answer, here is amusing query]:

Top Reputation users from ALL COUNTRIES of world:

https://data.stackexchange.com/stackoverflow/query/1250968/