2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

SELECT
  TagsRanking.TagName,
  TagsRanking.Score,
  STR(TagsRanking.Ranking) + '%' AS TopN
FROM (
  SELECT
    answers.OwnerUserId AS UserId,
    Tags.TagName AS TagName,
    SUM(answers.score) AS Score,
    NTILE(100) OVER(PARTITION BY Tags.TagName ORDER BY SUM(answers.score) DESC) AS Ranking
  FROM Posts answers
  JOIN PostTags ON answers.ParentId = PostTags.PostId
  JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName IN (
    SELECT
      Tags.TagName AS TagName
    FROM Posts answers
    JOIN PostTags ON answers.ParentId = PostTags.PostId
    JOIN Tags ON PostTags.TagId = Tags.Id
    WHERE answers.OwnerUserId = ##UserId##
    GROUP BY Tags.TagName
    HAVING SUM(answers.score) > 0
  )
  GROUP BY answers.OwnerUserId, Tags.TagName
) TagsRanking
WHERE TagsRanking.UserId = ##UserId##
ORDER BY TagsRanking.Ranking, TagsRanking.Score DESC, TagsRanking.TagName

fork query

edited jan 15

created jan 14 Roland Weisleder