Tag Expertise

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- This query lists in how many tags a user is the "top expert", with
-- the total number of answers and total score of answers in those tags.


-- list of the combined answer scores each user has 
-- for each of the tags they've answered. 
with scorePerTag as (
select   t.tagname, 
         u.id, 
         sum(pa.score) * 10 as tagscore,
         count(pa.score) as tagAnswers
from     users u
join     posts pa
on       pa.owneruserid = u.id -- user owns given answer
join     posts pq
on       pa.parentid = pq.id -- answer is answer to given question
join     tags t
on       pq.tags like '%' + t.tagname + '%' -- question uses given tag
group by t.tagname, u.id
),
-- list of the top score for each tag from the previous results. 
-- this query will only get the tag and score, so it has to be joined 
-- to the first query again in order to get user details. 
topScorePerTag as (
select   max(tagscore) as maxtagscore,
         tagname -- as the maximum is based on tag and not user, 
                 -- user details cannot be in this result set. 
from     scorePerTag 
where    taganswers > 2 -- only use records where the user answered 3+ times
group by tagname 
),
-- joins the above two tables to find the user who has the most 
-- answer score for each tag. 
expertList as (
select   spt.tagname, 
         spt.id, 
         spt.tagscore,
         spt.taganswers
from     scorepertag spt -- list of all tag scores for all users
join     topscorepertag tspt -- list of top tag scores
on       spt.tagname = tspt.tagname
and      spt.tagscore = tspt.maxtagscore -- filter spt by tspt
where    tagscore > 25 -- filter results for only high-scoring tags
and      taganswers > 2
)
select   id as [User Link],
         count(id) as 'Number of Tags',
         sum(taganswers) as 'Total Answers',
         sum(tagscore) as 'Total Answer Score'
from     expertList
group by id
order by count(id) desc, sum(tagscore) desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results