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
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'
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'
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 (
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
select rank() over(partition by countryid order by reputation desc) [pos]
) 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
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.