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?
;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