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 (
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.
expertList as (
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'
group by id
order by count(id) desc, sum(tagscore) desc