Developer Story, minimal tag scores for 1-5-10-20 Pct for all tags of the site

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

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

WITH UserTagScores AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    PostTags.TagId,
    SUM(Posts.Score) AS Score
  FROM Posts, PostTags
  WHERE
    Posts.ParentId = PostTags.PostId
  GROUP BY Posts.OwnerUserId, PostTags.TagId
  HAVING Posts.OwnerUserId > 0 AND COUNT(*) > 1
),
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;

/*
Pcts AS (
  SELECT 1 AS Pct
  UNION
  SELECT 5
  UNION
  SELECT 10
  UNION
  SELECT 20
)
SELECT
FROM Raw, Pcts

Enter Parameters

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