Query for getting top users on a specific tag by country sorted by tag score (Stack Overflow)

Question

I need a query for getting top users on a specific tag by country sorted by tag score on the Stack Overflow site.

For example.

Top users of JavaScript that live in the United States and sorted by JavaScript score.

It would be better for the tag and country to be parameters.

This is the closest query I could reach, but I couldn't apply it on a specific tag.

Sorry, I'm not a SQL developer to create the query myself.

This should be a popular question, but I researched for an hour and I didn't find any answer.

Answer 1

You need to join the Posts tables twice as only the questions (PostTypeid =1) have the tag info. From there you inner join posttags and tags. In the where clause you can then limit your result set to the tag and users you're interested in. Do note that the Users table doesn't have a country field. Users can enter location but that is not mandatory, nor is it backed by a country list. Users either don't or fill in whatever they dream-up. So keep that in mind when you interpret the result of this query

select top 1000 u.id as [User Link]
     , sum(p.score) [Total]
     , sum(case when p.posttypeid = 1 then p.score else null end) [Total Q]
     , sum(case when p.posttypeid = 2 then p.score else null end) [Total A]
     , count(*) [# posts]
from users u
inner join posts p on p.owneruserid = u.id
inner join posts q on q.id = nullif(p.parentid, p.id)
inner join posttags pt on pt.postid = q.id
inner join tags t on t.id = pt.tagid
where u.location like concat('%', ##country:string?United States##, '%')
and t.tagname = ##tag:string?javascript##
group by u.id
order by sum(p.score) desc

You'll notice that most high-rep JavaScript users got their rep from writing awesome answers, not from asking awesome questions.

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the admirable Monica Cellio.
Say "Hi" in SEDE chat.

Answer 2

There already is a fine answer - but I decided to add something too. I hope at least some of this stuff might be useful.

Some issues related to the type of query you want:

1. You cannot take the location field as something reliable. (E.g., I have seen many people who  have fictional places there. And without additional data, you can't distinguish between Georgia in the USA and the country Georgia.)
2. Location does not necessarily contain the country. For example, somebody might include only the city they're living in.
3. Tags are only on questions - some extra work is needed if you want to get score from answers (or both questions and answers).
4. It might be useful to restrict the query to users above certain reputation - at least if you're running it on a large site where the query might time out.

As examples I will use queries checking javascript on Stack Overflow. And I will include queries for set-theory on MathOverflow, too. (Quite often I prefer to test queries on smaller sites first - since the queries run faster. The same might be useful if you want to play around with those queries and modify them in various ways.)

Let us start with a simple approach - just checking whether the location contains the name of the country: MathOverflow and Stack Overflow.

Some comments regarding this query:

• By changing the parameter "types" you can choose counting only questions, only answers, or both of them. (By setting this parameter to 1 or 2 or 1,2.)
• The part q.Id=COALESCE(p.ParentId,p.Id) is included so that we know tags for answers, too.

As already mentioned, people can use various things in the location field. In the above example, I checked for the string "United States". So I missed people who have, for example, "USA" or "Austin, TX" as their location. For example, if you have a look at top users in the tag javascript, maybe Kennewick, WA isn't among the places you would think about when looking for people from the USA.

Making a thorough query that checks for various possibilities is quite a lot of effort. Here are examples of such queries that somebody done for Ukraine or Canada.

I tried to modify a bit the query linked here: How can I write a query DATA.SE to list top users from all countries in the world? (This query can be used for various countries.)

This is what I ended up with: MathOverflow and Stack Overflow.

For example, I have included "Kennewick, WA" - since I knew that I want such user(s) among the results. But if I run the same query for some other tag, I can easily miss many users.