Calculates rank by tag. Make tag name empty spaces for all tags
Q&A for developers and researchers interested in open data
-- 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, rank() over (partition by TagName order by score desc) rank FROM UserTagScores) SELECT TagName, Rank FROM userTagRank WHERE OwnerUserId = ##UserId## ORDER BY TagName