The tags where you can get most characteristically the most rep by answers

1

Please login or register to vote for this query.

(click on this box to dismiss)

It uses your SE Account Id, not your site account id!

Stack Overflow

Q&A for professional and enthusiast programmers

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

WITH VoteRep AS (
  SELECT 1 AS VoteTypeId, 15 AS Rep
  UNION
  SELECT 2, 10
  UNION
  SELECT 3, -2
),
RepByTag AS (
  SELECT PostTags.TagId, SUM(VoteRep.Rep) AS Count
  FROM Posts Q, Posts A, PostTags, Votes, VoteRep
  WHERE
    A.OwnerUserId = @UserId
    AND
    A.ParentId = Q.Id
    AND
    PostTags.PostId = Q.Id
    AND
    Votes.PostId = A.Id
    AND
    Votes.VoteTypeId = VoteRep.VoteTypeId
  GROUP BY PostTags.TagId
),
PostByTag AS (
  SELECT PostTags.TagId, COUNT(*) AS Count
  FROM Posts A, Posts Q, PostTags
  WHERE
    A.OwnerUserId = @UserId
    AND
    Q.Id = PostTags.PostId
    AND
    A.ParentId = Q.Id
  GROUP BY PostTags.TagId
),
Raw AS (
  SELECT
    Tags.TagName,
    Tags.Id AS TagId,
    RepByTag.Count AS Rep,
    PostByTag.Count AS Posts,
    1.0 * RepByTag.Count / PostByTag.Count AS Ratio
  FROM RepByTag, PostByTag, Tags
  WHERE
    RepByTag.TagId = PostByTag.TagId
    AND
    PostByTag.TagId = Tags.Id
),
Raw2 AS (
  SELECT TagName, TagId, Rep, Posts, Ratio,
    RANK() OVER (ORDER BY Ratio DESC) AS RatioRank,
    RANK() OVER (ORDER BY Posts DESC) AS PostNoRank
  FROM Raw
)
SELECT *--TagName, TagId, Rep, Posts, Ratio
FROM Raw2
ORDER BY RatioRank + PostNoRank, TagId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results