Shows the tag scores and per-tag post counts for a given user, with the top 9 tags _by non-CW post count_ selected for showing on the profile page, then ordered by tag score.
The top six of these actually make it to the profile page.
There are parameters to alter the post-count cutoff point and a flag to enable counting Community Wiki posts in the per-tag post counts. The defaults for these are what is actually used, but playing with these settings can help understanding the algorithm better.
Q&A for people interested in the history and origins of science and mathematics
WITH by_post_count AS (
t.TagName AS Tag
, COUNT(p.Id) AS [Post Count]
-- Tag score is the sum of non-CW answer scores for the tag
WHEN (p.PostTypeId = 2 AND p.CommunityOwnedDate IS NULL) THEN p.Score
) AS Score
, RANK() OVER (ORDER BY COUNT(p.Id) DESC) AS post_count_rank
FROM Posts p
-- If post is answer get tags via associated question, otherwise directly
INNER JOIN PostTags pt ON pt.PostId = COALESCE(p.ParentId, p.Id)
INNER JOIN Tags t ON pt.TagId = t.Id
p.OwnerUserId = ##userid:int##
AND p.PostTypeId IN (1, 2)
-- Set to 1 to include Community Wiki posts from the post counts
-- and scoring.
##countcw:int?0## != 0
OR p.CommunityOwnedDate IS NULL
GROUP BY t.TagName
is_considered AS (
, [Post Count]
WHEN post_count_rank <= ##cutoff:int?9## THEN 1
END AS [Considered]
RANK() OVER (PARTITION BY Considered ORDER BY Score DESC, [Post Count] DESC)
, [Post Count]
WHEN Considered = 1
AND (RANK() OVER (PARTITION BY Considered ORDER BY Score DESC) <= ##toptagcount:int?6##)
END AS [Top Tag]
ORDER BY Considered DESC, Score DESC, [Post Count] DESC;