Developer Story, Tag Score to Percentile

1

Please login or register to vote for this query.

(click on this box to dismiss)

Martial Arts Meta

Q&A about the site for students and teachers of all martial arts

DECLARE @TagId INT = (
  CASE
    WHEN ##TagId:int?0## > 0 THEN ##TagId##
    ELSE (SELECT Id FROM Tags WHERE TagName = ##TagName:string?-## )
  END
);

Print @TagId;

WITH Raw AS (
  SELECT Posts.OwnerUserId AS UserId, SUM(Posts.Score) AS Score
  FROM Posts, PostTags
  WHERE
    Posts.ParentId = PostTags.PostId
    AND
    PostTags.TagId = @TagId
    AND
    Posts.OwnerUserId > 0
  GROUP BY Posts.OwnerUserId
  HAVING COUNT(*) > 1
),
Raw2 AS (
  SELECT Score, COUNT(*) AS UserCount
  FROM Raw
  GROUP BY Score
),
Raw3 AS (
  SELECT
    Score, UserCount, SUM(UserCount) OVER (ORDER BY Score DESC) AS CumCount
  FROM Raw2
)
SELECT
  Score,
  UserCount,
  CumCount,
  100.0 * CumCount / (SELECT COUNT(*) FROM Raw) AS Pct
FROM Raw3
ORDER BY Score DESC

Enter Parameters

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