1

Please login or register to vote for this query.

(click on this box to dismiss)

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

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

view current version

edited sep 18 21

created sep 18 21 peterh (2)