11

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackoverflow.com/questions/350902/tags-that-i-am-in-the-top-5-answeres

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

-- userid: User Id "The id in the url of your Stack Oveflow user profile"
-- pos: Top position "What is the position you're interested in"

-- create a temp table to hold only the tagid's for the OP
-- this should reduce from 80,000 rows to join to way less.
create table #tags(id integer primary key
                  , tagname nvarchar(35)
                  )
                  
insert into #tags
select distinct 
       t.id
     , t.tagname
from tags t
inner join posttags pt on pt.tagid = t.id
inner join posts p on pt.postid = coalesce(p.parentid, p.id)
where p.owneruserid = ##userid## 


;with top_answerers as (
select a.owneruserid
     , t.tagname 
     , sum(a.score) [tag score]
     , rank() over(partition by t.tagname order by sum(a.score) desc ) as pos
from posts a
inner join posttags pt on pt.postid = a.parentid
inner join #tags t on t.id = pt.tagid -- join on the tags temp table
where a.posttypeid = 2 -- answer
and a.owneruserid is not null 
group by a.owneruserid
       , t.tagname
)

select tagname
     , pos
     , owneruserid as [User Link]
     , [tag score]
from top_answerers
where  owneruserid = ##userid?7031230##
and pos <= ##pos?10##
order by pos desc

fork query

edited aug 10 19

created jun 18 17 rene