Tag Expertise


Please login or register to vote for this query.

(click on this box to dismiss)


Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

-- 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, 
         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, 
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

Switch to meta site
loading Hold tight while we fetch your results