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


Please login or register to vote for this query.

(click on this box to dismiss)

Language Learning

Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

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 =
inner join users u on = 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

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results