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 (
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.
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.
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