3

Is there any way we can find the top users for a tag given in a particular time period? For example, someone was very active and rose to top 10 for certain tags in 2014 but then changed technologies or maybe went inactive. Information like this could be really helpful to be put on someone's resume.

5

The easy way would be to see if the "Top Users" page was archived somewhere. Here is an example of such a page. But you don't always get lucky with that.

Data dumps for most (but not all) of Stack Overflow's history can be found here.

You then should be able to run the following query by conradfrix on that data to get the rankings:

-- tag rankings
-- Calculates rank by tag. Make tag name empty spaces for all tags

DECLARE @tagName varchar(255) 
DECLARE @tagInput bit
SET @tagname = rtrim(##TagName:string##)

IF RTRIM(@tagname) = ''

   SET @tagInput = 0 
ELSE
   SET @tagInput = 1



;WITH UserTagScores
as 
(SELECT  
   a.OwnerUserId,
   t.TagName,
   SUM(CASE v.VoteTypeID
           WHEN 2 THEN 1
           WHEN 3 THEN  -1
   END) as  score

FROM 
   votes v
   INNER JOIN Posts a
   ON v.PostID = a.ID
   INNER JOIN Posts q
   ON a.parentId = q.id
   INNER JOIN PostTags pt
   ON q.id = pt.PostId
   INNER JOIN Tags T
   ON pt.TagId = t.id
WHERE
   v.voteTypeId in (2,3)  -- Up and down votes
   and
   a.PostTypeID = 2  -- Answers only
   AND
   (a.CommunityOwnedDate is null or 
    a.CommunityOwnedDate > v.CreationDate)
   AND 
   (@tagInput = 0
    or t.TagName = @TagName)
    AND a.OwnerUserId is not null
GROUP BY
   a.OwnerUserId,
   t.TagName)
, userTagRank as (
 SELECT
    TagName,
   OwnerUserId as [User Link],
    rank() over (partition by TagName order by score desc) rank
  FROM
    UserTagScores
  WHERE
    score > 5)
SELECT 
    *
FROM
    userTagRank 
ORDER BY
    TagName

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .