3

I would like to get the user IDs, user names, the number of posts, and the total score for a certain tag for each user in a specific country (sorted in descending order by score).

For example, in the search query I would enter "Lebanon" for country and "iOS" for the tag, and the result would be something like this (the first entry is real, my own!):

userID userName NumPosts score
766570 abbood 171 860
63343 mike1515 200 700
532342 superman 50 600

This should match with what appears on the user's profile, for example, for the first user:

Tags section

Help/References

  • I tried something like this, but it is very simplistic
  • This query basically gets the number of posts per user
Improve this question
4
  • IP addresses shouldn't be accessible to the users, only staff. Where do people list their country of origin? (I know that I never have). Are you talking about the Teams product?
    – W.O.
    Mar 2 at 6:07
  • @W.O. "Where do people list their country of origin". In their profile. E.g., you live in Squid central.
    – Franck Dernoncourt
    Mar 2 at 7:32
  • @FranckDernoncourt Fair enough. I'm just wary of reading too-much into data re the self selected group that would put their actual location there. Hmm, maybe they're the ones you see without the tin-foil hats.
    – W.O.
    Mar 3 at 4:40
  • A related older question: Query for getting top users on a specific tag by country sorted by tag score (Stack Overflow). There is the usual caveat that location field can contain various things (e.g., city rather than country). And the city with the same name can be in various countries. Handling such exceptions can be a rather cumbersome task. As am example, I will add this query: data.stackexchange.com/stackoverflow/query/1400175 It arose from the discussion here: meta.stackexchange.com/q/348060
    – Martin
    Mar 3 at 11:12

1 Answer 1

Reset to default
4

This query does the job. The tricky thing is that only answers count for the score, and Community Wiki posts are excluded. Also, the query assumes everybody uses the same spelling for the location. Some users put a fake location, or maybe just the town or region, or an abbreviation of the country.

enter image description here

I've added the Location column, because as @Martin noted in the comments, toponyms are often reused (especially in the USA, by all those expatriates). If you scroll further down, you'll see 'Lebanon, TN, USA' and 'Lebanon, IL, USA'.

Improve this answer
4
  • In the example you've used, it is perhaps worth mentioning that this would include users from Lebanon, NH and Lebanon, NJ, if some users from there are active in the given tag. (It doesn't seem to be case for the tag [ios] - but maybe for some other tags the situation might be different.)
    – Martin
    Mar 3 at 11:18
  • @Martin yeah, Georgia (Eurasian country and US state) is another example ...
    – Glorfindel Mod
    Mar 3 at 11:33
  • @Glorfindel can you edit the query to also include as a column the date of the most recent post?
    – abbood
    Mar 15 at 8:12
  • @abbood sure, done
    – Glorfindel Mod
    Mar 15 at 10:18

You must log in to answer this question.

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