Find top User for a Country for a specific Month/Year

Question

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?

Answer 1

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 10 -- 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: