1

Please login or register to vote for this query.

(click on this box to dismiss)

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?199549##;

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
)
SELECT *
FROM Raw
ORDER BY Ratio DESC, TagId;

fork query

edited apr 6 19

created apr 5 19 peterh - PLEASE DELETE THIS ACCOUNT