How to find top users by several tags?

Question

I know it is possible to find top users by one tag, but is it also possible to use several tags: to cross reference users?
For example, on Stack Overflow I want to find the top user for phonegap AND jquery-mobile. How could I do this?

Answer 1

The following query gives you that outcome if you're happy with using the Data Explorer

;with qtags as (
select distinct pt.postid
from posttags pt 
inner join tags t 
        on t.id = pt.tagid
where t.tagname in ('phonegap', 'jquery-mobile') -- add more tags here
)
-- overall query to sum the results
select main.owneruserid as [User Link]
     , sum(q) as [question score]
     , sum(a) as [answer score]
     , sum(q) + sum(a) as [total score]
from (
    -- first union for questions
    select p.owneruserid
         , sum(p.score) as [q]
         , 0 as [a]
    from posts p 
    inner join qtags qt on qt.postid = p.id
    group by p.owneruserid
    union 
    -- second union for answers
    select p.owneruserid
         , 0 as [q]
         , sum(p.score) as [a]
    from posts p 
    inner join qtags qt on qt.postid = p.parentid 
    where p.posttypeid = 2 -- A
    group by p.owneruserid
) as main
group by main.owneruserid
order by sum(q) + sum(a) desc  -- total

How does this work?

I first get a the set of Posts id for the question that have the tags you're interested in. Then use those id's to join with the posts table twice, once on posts.id for the questions and once on posts.parentid for the answers. By grouping on user and calculating the sum of the question and answer score I get the total score for those tags. By ordering descending the first row holds the user with the highest score.

Result on Jan, 24th 2015

User Link       | question score | answer score | total score
Gajotres        |             0  |        3599  |       3599
Jasper          |             0  |        1741  |       1741
Omar            |             7  |        1638  |       1645
Phill Pafford   |            40  |        1319  |       1359
Simon MacDonald |             0  |        1135  |       1135
naugtur         |            45  |         486  |        531