1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for peer programmer code reviews

--@DaysSinceActivity is the amount of days that you would like to scan for activity
--@NumberOfUsers is the number of users you want to return.


SELECT 
    TOP ##NumberOfUsers:int?1000##
    Users.Id AS [User Link],
    Users.DisplayName AS UserName,
    COUNT(Posts.Id) AS [Total Answers],
    CAST(AVG(CAST(Score AS float)) AS NUMERIC(6,2)) AS [Average Answer Score],
    RANK() OVER (ORDER BY (CAST(AVG(CAST(Score AS float)) AS NUMERIC(6,2))) DESC) AS [Avg Score Ranking],
    RANK() OVER (ORDER BY (COUNT(Posts.Id)) DESC) AS [Total Answer Rank]

     
    
FROM
    Posts
  INNER JOIN
    Users ON Users.Id = OwnerUserId
WHERE 
    PostTypeId = 2 AND CommunityOwnedDate IS NULL AND ClosedDate IS NULL
    AND Users.LastAccessDate > DATEADD(DAY, -##DaysSinceActivity:int?31##, GETDATE())
GROUP BY
    Users.Id, Users.DisplayName
HAVING
    COUNT(Posts.Id) > 10
ORDER BY
    [Average Answer Score] DESC

view current version

edited sep 19 14

created jan 15 14 Malachi