0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH
  UserTagScores AS (
    SELECT OwnerUserId AS UserId, TagId, SUM(Score) AS Scores
    FROM Posts INNER JOIN PostTags ON PostTypeId=2 AND 0<Score AND ParentId=PostId
    GROUP BY OwnerUserId, TagId
  ),
  UserTagRankScore AS (
    SELECT DisplayName AS UserName, TagId, RANK () OVER (PARTITION BY TagId ORDER BY Scores DESC, UserId) AS Rank, Scores
    FROM UserTagScores LEFT OUTER JOIN Users ON UserId=Id
  )
SELECT TagName, Rank1.UserName AS User1, Rank1.Scores AS Score1, Rank2.UserName AS User2, Rank2.Scores AS Score2, Rank3.UserName AS User3, Rank3.Scores AS Score3
FROM Tags
LEFT OUTER JOIN UserTagRankScore AS Rank1 ON Rank1.Rank=1 AND Id=Rank1.TagId
LEFT OUTER JOIN UserTagRankScore AS Rank2 ON Rank2.Rank=2 AND Id=Rank2.TagId
LEFT OUTER JOIN UserTagRankScore AS Rank3 ON Rank3.Rank=3 AND Id=Rank3.TagId
ORDER BY Rank1.Scores DESC, Rank2.Scores DESC, Rank3.Scores DESC

fork query

edited jan 10 22

created jan 10 22 sayuri