Tag Experts


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 the user who has achieved the highest combined answer score
-- for each tag and also answered at least three questions with that tag. 
-- Essentially, this query should tell you who knows the most about 
-- each tag (provided the tag is popular enough to fit these criteria).
-- In the event of a tie, both users are displayed.

-- 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. 
select   spt.tagname, 
         spt.id as [User Link], 
         spt.tagscore as 'Tag Answer Score',
         spt.taganswers as 'Tag Answers'
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
order by spt.tagname

Enter Parameters

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