How to improve your Developer Story effectively?

1

Please login or register to vote for this query.

(click on this box to dismiss)

Vegetarianism

Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more

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

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

CREATE TABLE #TagScorePct (
  TagId INT NOT NULL,
  Score INT NOT NULL,
  Pct FLOAT NOT NULL,
  PRIMARY KEY(TagId, Score)
);

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

WITH TagScoreUserCountRaw AS (
  SELECT TagId, Score, COUNT(*) AS UserCount
  FROM #UserTagScores
  GROUP BY TagId, Score
),
TagScoreUserCount AS (
  SELECT
    TagId,
    Score,
    UserCount,
    SUM(UserCount) OVER (PARTITION BY TagId ORDER BY Score DESC)
      AS CumCount
  FROM TagScoreUserCountRaw
),
TagUserCount AS (
  SELECT TagId, COUNT(*) AS UserCount
  FROM #UserTagScores
  GROUP BY TagId
)
INSERT INTO #TagScorePct
SELECT
  TagScoreUserCount.TagId,
  TagScoreUserCount.Score,
  100.0 * TagScoreUserCount.CumCount / TagUserCount.UserCount AS Pct
FROM TagScoreUserCount, TagUserCount
WHERE
  TagScoreUserCount.TagId = TagUserCount.TagId;

WITH Pcts AS (
  SELECT 1 AS Pct
  UNION
  SELECT 5
  UNION
  SELECT 10
  UNION
  SELECT 20
),
Raw AS (
  SELECT #TagScorePct.TagId, Pcts.Pct, MIN(Score) AS MinScore
  FROM #TagScorePct, Pcts
  WHERE #TagScorePct.Pct < Pcts.Pct
  GROUP BY #TagScorePct.TagId, Pcts.Pct
)
SELECT Tags.TagName, Raw.*
FROM Raw, Tags
WHERE Raw.TagId = Tags.Id
ORDER BY MinScore DESC

Enter Parameters

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