1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited apr 20 17

created apr 20 17 JNat