As the title says, this grabs the percentage of rep a user earned in a given tag. Note that rep earned in tags is not mutually exclusive, so percentages are gonna exceed 100%, and total rep is gonna exceed the actual rep. This does not take into account suggested edits to tag wikis, and tag excerpts. Also does not account for the edge case of rep lost due to spam/offensive posts.
Q&A for professional and enthusiast programmers
DECLARE @uid INT = ##UserId## DECLARE @userrep FLOAT = (SELECT u.Reputation FROM Users u WHERE u.Id = @uid) SELECT t.TagName AS [Tag], (SELECT (COALESCE(votesonquestions.rep, 0) + COALESCE(acceptinganswers.rep, 0) + COALESCE(earnedbounties.rep, 0) + COALESCE(offeredbounties.rep, 0) + COALESCE(votesonanswers.rep, 0) + COALESCE(approvededitsinq.rep, 0) + COALESCE(approvededitsina.rep, 0)) * 100 / @userrep) AS [% of total rep on tag], (SELECT COALESCE(votesonquestions.rep, 0) + COALESCE(acceptinganswers.rep, 0) + COALESCE(earnedbounties.rep, 0) + COALESCE(offeredbounties.rep, 0) + COALESCE(votesonanswers.rep, 0) + COALESCE(approvededitsinq.rep, 0) + COALESCE(approvededitsina.rep, 0)) AS [Total rep in tag], COALESCE(votesonquestions.rep, 0) AS [From votes on questions], COALESCE(acceptinganswers.rep, 0) AS [From accepting answers], COALESCE(earnedbounties.rep, 0) AS [From earned bounties], COALESCE(offeredbounties.rep, 0) AS [From offered bounties], COALESCE(votesonanswers.rep, 0) AS [From votes on answers], COALESCE(approvededitsinq.rep, 0) AS [From approved suggested edits on questions], COALESCE(approvededitsina.rep, 0) AS [From approved suggested edits on answers] FROM Tags t -- Rep from votes on own questions LEFT OUTER JOIN (SELECT t.Id, SUM(CASE WHEN v.VoteTypeId = 2 THEN 5 WHEN v.VoteTypeId = 3 THEN -2 ELSE 0 END ) AS [rep] FROM Posts p JOIN Votes v ON v.PostId = p.Id JOIN PostTags pt ON pt.PostId = p.Id JOIN Tags t ON t.Id = pt.TagId WHERE p.OwnerUserId = @uid AND p.PostTypeId = 1 GROUP BY t.Id) AS votesonquestions ON t.Id = votesonquestions.Id -- Rep from accepting answers in own questions LEFT OUTER JOIN (SELECT t.Id, SUM(CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN 2 ELSE 0 END ) AS [rep] FROM Posts p JOIN PostTags pt ON pt.PostId = p.Id JOIN Tags t ON t.Id = pt.TagId WHERE p.OwnerUserId = @uid AND p.PostTypeId = 1 GROUP BY t.Id ) AS acceptinganswers ON t.Id = acceptinganswers.Id -- Rep from bounties earned on answers LEFT OUTER JOIN (SELECT t.Id, SUM(CASE WHEN v.BountyAmount IS NOT NULL THEN v.BountyAmount ELSE 0 END ) AS [rep] FROM Posts p JOIN Votes v ON v.PostId = p.Id JOIN PostTags pt ON pt.PostId = p.ParentId JOIN Tags t ON t.Id = pt.TagId WHERE p.OwnerUserId = @uid AND p.PostTypeId = 2 GROUP BY t.Id) AS earnedbounties ON t.Id = earnedbounties.Id -- Rep lost in offered bounties LEFT OUTER JOIN(SELECT t.Id, SUM(CASE WHEN v.BountyAmount IS NOT NULL AND v.UserId = @uid THEN -v.BountyAmount ELSE 0 END ) AS [rep] FROM Posts p JOIN Votes v ON v.PostId = p.Id JOIN PostTags pt ON pt.PostId = p.Id JOIN Tags t ON t.Id = pt.TagId AND p.PostTypeId = 1 GROUP BY t.Id) AS offeredbounties ON t.Id = offeredbounties.Id -- Rep from votes in answers LEFT OUTER JOIN(SELECT t.Id, SUM(CASE WHEN v.VoteTypeId = 1 THEN 15 WHEN v.VoteTypeId = 2 THEN 10 WHEN v.VoteTypeId = 3 THEN -2 ELSE 0 END ) AS [rep] FROM Posts p JOIN Votes v ON v.PostId = p.Id JOIN PostTags pt ON pt.PostId = p.ParentId JOIN Tags t ON t.Id = pt.TagId WHERE p.OwnerUserId = @uid AND p.PostTypeId = 2 GROUP BY t.Id) AS votesonanswers ON t.Id = votesonanswers.Id -- Rep from approved suggested edits on questions LEFT OUTER JOIN(SELECT t.Id, SUM(CASE WHEN se.ApprovalDate IS NOT NULL AND se.OwnerUserId = @uid THEN 2 ELSE 0 END ) AS [rep] FROM SuggestedEdits se JOIN Posts p ON p.Id = se.PostId JOIN PostTags pt ON pt.PostId = p.Id JOIN Tags t ON t.Id = pt.TagId GROUP BY t.Id) AS approvededitsinq ON t.Id = approvededitsinq.Id -- Rep from approved suggested edits on answers LEFT OUTER JOIN(SELECT t.Id, SUM(CASE WHEN se.ApprovalDate IS NOT NULL AND se.OwnerUserId = @uid THEN 2 ELSE 0 END ) AS [rep] FROM SuggestedEdits se JOIN Posts p ON p.Id = se.PostId JOIN PostTags pt ON pt.PostId = p.ParentId JOIN Tags t ON t.Id = pt.TagId GROUP BY t.Id) AS approvededitsina ON t.Id = approvededitsina.Id GROUP BY t.TagName, votesonquestions.rep, acceptinganswers.rep, earnedbounties.rep, offeredbounties.rep, votesonanswers.rep, approvededitsinq.rep, approvededitsina.rep ORDER BY [% of total rep on tag] DESC