6

Please login or register to vote for this query.

(click on this box to dismiss)

A list of the top users with the highest average answer score excluding community wiki / closed posts or users with fewer than 10 answers

Q&A about the site for serious players and enthusiasts of chess

WITH UserAvgAnswerScore AS (
    SELECT 
        Users.Id AS UserId,
        Count(Posts.Id) AS Answers,
        CAST(AVG(CAST(Score AS float)) as numeric(6,2)) AS [Average Answer Score]
    FROM
        Posts
      INNER JOIN
        Users ON Users.Id = OwnerUserId
    WHERE
        PostTypeId = 2 and CommunityOwnedDate is null and ClosedDate is null
    GROUP BY
        Users.Id, DisplayName
    HAVING
        Count(Posts.Id) > 10
)
SELECT TOP ##n:int?500##
    RANK() OVER (Order BY [Average Answer Score] DESC) AS Rank,
    UserId AS [User Link],
    Answers,
    [Average Answer Score]
FROM
    UserAvgAnswerScore
ORDER BY
    [Average Answer Score] DESC;

fork query

edited jan 15 14 200_success

created may 25 10 sam.saffron