http://meta.stackexchange.com/questions/252924/recreate-a-query-for-the-tags-associated-with-a-user-based-on-time-filter
Q&A about the site for movie and tv enthusiasts
declare @country nvarchar(100) set @country = ##country:string?England## set @country = '%'+ @country +'%'; -- all users with their reputation with topusers as ( select p.owneruserid ,sum(case v.votetypeid when 2 then case p.posttypeid when 1 then 10 else 10 end else 0 end) + sum(case v.votetypeid when 3 then -2 else 0 end) + sum(case v.votetypeid when 1 then 15 else 0 end) as tot 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 ) -- all users with rep and tags order by total rep , row numbered , toptagsusers as (select p.owneruserid , t.tagname , row_number() over ( partition by p.owneruserid order by sum(case v.votetypeid when 2 then case p.posttypeid when 1 then 10 else 10 end else 0 end) + sum(case v.votetypeid when 3 then -2 else 0 end) + sum(case v.votetypeid when 1 then 15 else 0 end) desc) as rownum from tags t inner join posttags pt on pt.tagid = t.id inner join posts p on pt.postid in (p.id, p.parentid) inner join votes v on v.postid = p.id 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 group by p.owneruserid , t.tagname ) -- join users and tags 3 times for the rank 1, 2 and 3 tags select -- top 100 row_number() over(order by tu.tot desc) as ranking , tu.tot , tu.owneruserid as [User Link] , tt1.tagname , tt2.tagname , tt3.tagname from topusers tu inner join toptagsusers tt1 on tu.owneruserid = tt1.owneruserid inner join toptagsusers tt2 on tu.owneruserid = tt2.owneruserid inner join toptagsusers tt3 on tu.owneruserid = tt3.owneruserid where tt1.rownum = 1 and tt2.rownum = 2 and tt3.rownum = 3 order by tu.tot desc