0

Please login or register to vote for this query.

(click on this box to dismiss)

Determines where you rank in a given tag as of the last data load

Q&A for those using, extending or developing Emacs

WITH
CombinedTags(Id, TagName) as (
  SELECT Id, TagName
    FROM Tags
   UNION
  SELECT Tags.Id, TargetTagName
    FROM TagSynonyms
    JOIN Tags on SourceTagName = TagName
),
MyPosts as (
  SELECT Id
    FROM Posts
   WHERE OwnerUserId = ##UserId##
     AND PostTypeId = 1 -- Question
   UNION
  SELECT ParentId as Id
    FROM Posts
   WHERE OwnerUserId = ##UserId##
     AND PostTypeId = 2 -- Answer
),
MyTags as (
  SELECT DISTINCT TagId, TagName
    FROM MyPosts
    JOIN PostTags ON PostId = MyPosts.Id
    JOIN CombinedTags ON TagId = CombinedTags.Id
)
SELECT TagName, COUNT(*)
  FROM MyTags
 GROUP BY TagName
 ORDER BY 2 DESC
/*
SELECT TOP 51
  STR(NTILE(100) OVER(ORDER BY SUM(answers.score) DESC), 3) + '%' AS [Top],
  ROW_NUMBER() OVER(ORDER BY SUM(answers.score) DESC) AS [Ranking],
  Users.Id AS [User Link],
  SUM(answers.Score) AS [Total Score]
FROM
  Tags
JOIN
  PostTags
ON
  Tags.Id = PostTags.TagId AND Tags.TagName = '##TagName##'
JOIN
  Posts questions
ON
  questions.Id = PostTags.PostId AND questions.CommunityOwnedDate IS NULL
JOIN
  Posts answers
ON
  answers.ParentId = questions.Id AND
  answers.Score <> 0 AND
  answers.OwnerUserId IS NOT NULL AND
  answers.CommunityOwnedDate IS NULL
JOIN
  Users
ON
  answers.OwnerUserId = Users.Id
GROUP BY
  Users.Id
ORDER BY
  CASE Users.Id WHEN ##UserId## THEN 1 ELSE 0 END DESC,
  [Total Score] DESC
*/

fork query

edited apr 20 Hosam Aly

created oct 31 11 Tim Stone