Top Users in the Game of Thrones Tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT TOP 10
       TagUsers.UserLink AS [User Link],
       TagUsers.ScorePercentage AS [Score Percentage],
       TagUsers.CountPercentage AS [Count Percentage],
       (TagUsers.ScorePercentage + TagUsers.CountPercentage) / 2 AS [Total Percentage]
FROM
(
    SELECT a.OwnerUserId AS UserLink,
           IIF(SUM(a.Score)  <= 1000, SUM(a.Score) / 10, 100) AS ScorePercentage,
           IIF(COUNT(a.Score) <= 200, COUNT(a.Score) / 2, 100) AS CountPercentage
    FROM Posts a, 
         Posts q
         INNER JOIN PostTags qt ON q.Id = qt.PostId
         INNER JOIN Tags t ON t.Id = qt.TagId
    WHERE a.ParentId = q.Id
      AND a.PostTypeId = 2
      AND a.CommunityOwnedDate IS NULL
      AND t.TagName = 'a-song-of-ice-and-fire'
    GROUP BY a.OwnerUserId
) TagUsers
ORDER BY (TagUsers.ScorePercentage + TagUsers.CountPercentage) / 2 DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results