2

I'm looking to recreate a SEDE query that represents the tags associated with a user based on a specific time filter. Consider, for example, the list of users sorted by reputation, filtered by week:

enter image description here

I would like to create a query that would return the TOP 3 tags associated with each user based on this time filter. In the above example, the query would return , and for user 811.

Since, I assume, the tags could represents any type of post by the specific user (question or answer), I have no clue how to join, filter and bend the tables to my will...

I know DateAdd("d",-7,<today>) might play a role when dealing with the "week" filter. Not sure how <today> can be inherent in a SEDE query without specifying it manually.

1 Answer 1

5

It took a while to get this done but I couldn't find a more elegant way than this query:

-- 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
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 
)
-- 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 5 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 
       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

Be aware that I didn't do a full reputation calculation, I limited myself to Accepts, Up and down votes.

The result for this query between 1 and 7 april 2015 looks like this:

top users based on rep with their 3 best tags

You must log in to answer this question.

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