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!

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

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