Your developer story percentile estimator

1

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

DECLARE @UserId INT = (
  SELECT Id FROM Users WHERE AccountId = ##SEAccountId:int##
);

CREATE TABLE #Raw (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  Score INT NOT NULL,
  PRIMARY KEY(UserId, TagId)
);

INSERT INTO #Raw
SELECT
  Posts.OwnerUserId AS UserId,
  PostTags.TagId,
  SUM(Posts.Score) AS Score
FROM PostTags, Posts
WHERE Posts.ParentId = PostTags.PostId
GROUP BY Posts.OwnerUserId, PostTags.TagId
HAVING COUNT(*) > 1 AND Posts.OwnerUserId > 0;

WITH Raw2 AS (
  SELECT TagId, Score, COUNT(*) AS Count
  FROM #Raw
  GROUP BY TagId, Score
),
TagUserCount AS (
  SELECT TagId, COUNT(*) AS Count
  FROM #Raw
  GROUP BY TagId
),
TagScoreDist AS (
  SELECT
    TagId,
    Score,
    Count,
    SUM(Count) OVER (PARTITION BY TagId ORDER BY Score DESC) AS CumCount
  FROM Raw2
)
SELECT
  Tags.TagName,
  Tags.Count AS TagQCount,
  TagUserCount.Count AS TagUserCount,
  #Raw.Score AS YourTagScore,
  TagScoreDist.CumCount - TagScoreDist.Count AS '> You',
  TagScoreDist.Count AS '== You',
  TagUserCount.Count - TagScoreDist.CumCount AS '< You',
  100.0 * TagScoreDist.CumCount / TagUserCount.Count AS YourPct,
  'https://data.stackexchange.com/stackoverflow/query/1462623?TagId='
    + CAST(Tags.Id AS VARCHAR(MAX)) + '|Click' AS PctDistro
FROM TagScoreDist, #Raw, Tags, TagUserCount
WHERE
  #Raw.UserId = @UserId
  AND
  #Raw.Score = TagScoreDist.Score
  AND
  TagScoreDist.TagId = #Raw.TagId
  AND
  Tags.Id = TagScoreDist.TagId
  AND
  TagUserCount.TagId = #Raw.TagId
ORDER BY YourPct, Tags.Id;

Enter Parameters

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