What percentile am I in for a given tag?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Determines where you rank in a given tag as of the last data load

Stack Overflow

Q&A for professional and enthusiast programmers

-- What percentile am I in for a given tag?
-- Determines where you rank in a given tag as of the last data load
-- TODO: add number of answer column

DECLARE @UserID Int = ##UserID?9449426##
DECLARE @TagName Nvarchar(Max) = '##TagName?reactjs##'

;

WITH Ranking AS (
  SELECT
    RANK() OVER(ORDER BY SUM(a.score) DESC) AS [Ranking],
    u.Id AS [User Link],
    SUM(a.Score) AS [Total Score],
    COUNT(a.Id) AS [Answers]
  FROM Tags t
  JOIN PostTags pt ON t.Id = pt.TagId AND t.TagName = @TagName
  JOIN Posts q ON q.Id = pt.PostId AND q.CommunityOwnedDate IS NULL
  JOIN Posts a ON
    a.ParentId = q.Id AND
    a.OwnerUserId IS NOT NULL AND
    a.CommunityOwnedDate IS NULL
  JOIN Users u ON a.OwnerUserId = u.Id
  GROUP BY u.Id
),

Percentile AS (
  SELECT
    Ranking.*,
    cast(ROUND(
      CAST(ROUND(Ranking.[Ranking], 6) AS numeric(36,6))
      /
      CAST(ROUND((SELECT MAX(Ranking) FROM Ranking), 6) as numeric(36,6))
      * 100.0
      , 6) as numeric(36, 6)
    ) AS [Top]
  FROM Ranking
)

SELECT TOP 300
  [Ranking],
  [Top],
  [User Link],
  [Total Score],
  [Answers]
FROM Percentile
ORDER BY
  IIF([User Link] = @UserID, 1, 0) DESC,
  [Total Score] DESC

Enter Parameters

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