Tag Ranking Percentile

1

Please login or register to vote for this query.

(click on this box to dismiss)

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

Moderators

Q&A for community managers, administrators, and moderators

-- tag ranking percentile
-- Calculates rank percentile by tag as is in the developer story.

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

 
;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 
   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
  WHERE
   score >= 3)
SELECT 
   TagName,
   100.0 * rank / (SELECT COUNT (*) FROM userTagRank) as percentile
FROM
   userTagRank 
WHERE 
   OwnerUserId = ##UserId##
ORDER BY
   TagName

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results