2

I want to see who has the highest score in a specific tag (for example c#) in my country.

Is there any query or script for that?

I've found this query, but it doesn't work. Can any one take a look at it and see what's its problem?

I also have found this query which does similar, however gets users which only have a tag badge.

  • just for curiousity, why would you want to do this? – user226423 Aug 17 '13 at 6:57
  • @Damien - One of them is quickly finding real pros near my area. Maybe we become co-workers in a project... – Amin Saqi Aug 17 '13 at 7:07
  • I am not sure how you would make contact with them considering many use pseudonyms, often do not have their contact details showing and remembering that there is no private messaging (thankfully) on any SE site. – user226423 Aug 17 '13 at 7:09
  • 2
    Unless you're looking to hire people (careers.stackoverflow.com), you have no reason to be doing this. Stalking people is unethical, and probably illegal. – Cody Gray Aug 17 '13 at 8:14
  • 1
    @CodyGray - No, I'm just interested in examining myself in a tag with other users in my area... Is it illegal...?! – Amin Saqi Aug 17 '13 at 8:34
  • 3
    The only real way to do that would be to pick a tag, say asp.net, and then check out the list of top users for that tag. You'll have to look at each person's profile to see where they're from, but that's about all you can do, since listing a location is optional. And lying is acceptable. – Cody Gray Aug 17 '13 at 8:46
  • @CodyGray "and lying is acceptable." Gads, you mean I haven't be obfuscating all along? Fake real name -> – Howard Pautz Sep 16 '14 at 22:17
2

Determining the score of a group of users in a tag is relatively easy because that only requires a sum of the score field per post per user. This query does exactly that:

select u.id as [User Link]
     , sum(score) as totalscore
from posts p
inner join posttags pt on pt.postid = p.id 
                       or pt.postid = p.parentid
inner join tags t on t.id = pt.tagid
inner join users u on u.id = p.owneruserid
where tagname = ##tag1:string## collate SQL_Latin1_General_CP1_CI_AI 
and location like '%'+ ##location:string## + '%'  collate SQL_Latin1_General_CP1_CI_AI 
group by u.id
order by sum(score) desc

To get an approximate reputation you need to join with the votes table and then this becomes more complex quickly.

Do notice that location is a user maintained field, there is no guarantee the user is near that Location at all.

| improve this answer | |

You must log in to answer this question.

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