2

I've came across Finding Top Users by country regarding a query to know who is in the top X of a given country.

I would like to know how to improve this query and also know the top reputation earner for a certain MONTH, YEAR or even WEEK.

Is it possible you think?

  • 1
    Not really, only a user's current rep is available via SEDE. You could try to do it approximately by looking at all their posts votes. – Robert Longson Oct 16 '15 at 7:49
  • @RobertLongson Thank for the answer :) – Yassin Hajaj Oct 16 '15 at 17:36
3

You can't get the exact reputation because not all events are public in SEDE (your own down voting for example) but if you're happy with just the largest rep gainers (up-, down- and accept votes) this query should enable you to get the reputation rank between any start- and end date for users that made their location public.

declare @country nvarchar(100)
set @country = ##country:string?England##
set @country = '%'+ @country +'%'

select owneruserid as [User Link]
     , row_number() over(order by rep desc) as ranking
     , rep
from (
select p.owneruserid
     ,sum(case v.votetypeid when 2 then -- upvote
           case p.posttypeid when 1 then 5 -- Q
                             else 10 end -- A
           else 0 end) 
       + sum(case v.votetypeid when 3 then -2 else 0 end) -- downvote
       + sum(case v.votetypeid when 1 then 15 else 0 end) as rep -- accept
from posts p 
inner join votes v on v.postid = p.id
inner join users u on u.id = p.owneruserid
where v.creationdate between ##start:string?2015-05-01## 
                         and ##end:string?2015-05-07##
and   v.votetypeid in (1,2,3)
and p.owneruserid is not null
and u.location like @country collate SQL_Latin1_General_CP1_CI_AI
group by p.owneruserid 
) as rawdata
order by ranking

The result will look like this:

top users per country

  • Works fine now ! Thank you :) – Yassin Hajaj Oct 18 '15 at 12:44
  • I didn't like that solution but I provided a different one @YassinHajaj Keep in mind that England will know also match new england – rene Oct 18 '15 at 12:45
  • Not a problem. I'm from Belgium so it will do :D. Thanks again for your help. – Yassin Hajaj Oct 18 '15 at 12:45

You must log in to answer this question.

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