1

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.

  • 1
    Why are you excluding greecer and not, say, greecen? – Glorfindel May 17 at 19:19
  • I don't know about this field, but for the tag field the tags are enclosed in angle brackets. So to not match "javascript" (false positive match) when using "java" in LIKE, the angle brackets must be included (<java>). Example (different from the Java/JavaScript example): <.net><.net-core><class-library><.net-standard> – P.Mort. - forgot Clay Shirky_q May 18 at 15:17
  • @P.Mort.-forgotClayShirky_q I've updated question. whatever I exclude from search, is really offtopic here. my main question is different thing. – p t May 19 at 13:51
  • and for each country want to have specific LIKE clauses to get an as accurate result as possible? And you want only the single Top user for that country, right? So if you added 5 countries your query will have 5 rows. – rene May 19 at 14:21
  • Somewhat related: Finding Top Users by country. I have also seen this query: [User List: Top N - Ukraine- with false positive exclusions ](data.stackexchange.com/mathoverflow/query/157038/…). Somewhat similar query for Slovakia was discussed a bit in chat: chat.meta.stackexchange.com/transcript/1223/2020/5/11 – Martin May 20 at 6:21
6

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:

countries with its top user

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.

| improve this answer | |
  • Excellent Rene ! Even though your answer & query is overly superb, i have to ask few things I couldnt understand, and would be nice if you added that in answer. my questions:(1) Per my understanding, the cities listed there are examples, when user doesnt use i.e. greece or slovak in their description, but use cities, so we recognize them by cities. Is that correct? – p t Jun 12 at 16:45
  • (2) in country_writings block athens is on first place and then followed by the coutnry name, but then slovak is on first place and is followed by it's cities ( i.imgur.com/yt0c0TW.png ) . does that ordering matters? does it matters what will be on first line where altname is written? why athens is there, and say, not greece, or slovak or banska ? – p t Jun 12 at 16:45
  • (3) you said about alternative approach using / . let's say we don't excluding anything with / inside it, so that alternative way is also acceptable if you added in your answer the example query using that approach. (4) the main matter for me is the "performance" of the query, let's say if we want to query for 10-20 countries, will that query "time-out"? i have seen some cases where query is not being executed (takes too long and 'time-outs'). – p t Jun 12 at 16:52
  • @pt Answer to that first comment is yes. Answer to your second comment is: order doesnt matter but the first row needs to have column labels but the actual values don't matter. Answer to 3rd comment: I can't predict execution time but it is worth keeping in mind that with each country_writing row you add, the SQL engine willl have to compare # of non empty Location records. So if you have 10 users and 1 country_writing, 10 compares are done, with 10 users and 10 country_writings 100 compares are done. So that number grows rapidly. Not much we can do about that. – rene Jun 12 at 16:59
  • @pt that looks right to me, also edited the answer a bit – rene Jun 12 at 17:59
  • Why 'slovak' in the first table? Shouldn't it be 'slovakia'? And alternative spellings 'slovak' and 'slovak republic'? – P.Mort. - forgot Clay Shirky_q Jun 12 at 18:30
  • @P.Mort.-forgotClayShirky_q I took what Martin gave me in the SEDE room but didn't check with him if I used it correctly. I'll check with him. – rene Jun 12 at 18:39
  • @P.Mort.-forgotClayShirky_q I have used slovak as the string which is likely to catch both Slovakia and Slovak Republic. (And, I hoped, unlikely to catch stuff which is unrelated to Slovakia.) This query - and also some other related queries - were mentioned in SEDE a few times: chat.meta.stackexchange.com/transcript/1223/2020/5/11 chat.meta.stackexchange.com/transcript/1223/2020/5/20 chat.meta.stackexchange.com/transcript/1223/2020/6/12 – Martin Jun 12 at 18:59
  • 1
    @martin, @ rene - with collaboration to my friend, here what we wrote ! :)) : data.stackexchange.com/stackoverflow/query/1250968 – p t Jun 12 at 19:40
0

[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/

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .