0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jun 3 Eklavya

created oct 16 15 rene