Top users by average answer score

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lists all users with at least 10 answers, ordered by average score

User Experience

Q&A for user experience researchers and experts

with percentages as (
  select
    u.Id as UserId,
    count(posts.Id) as Posts,
    sum(posts.Score) * 1.0 / count(posts.Id) as AvgScore,
    (
     (SELECT MAX(Score) FROM
       (SELECT TOP 50 PERCENT Score FROM Posts WHERE OwnerUserId = u.Id ORDER BY Score) AS BottomHalf)
     +
     (SELECT MIN(Score) FROM
       (SELECT TOP 50 PERCENT Score FROM Posts WHERE OwnerUserId = u.Id ORDER BY Score DESC) AS TopHalf)
    ) / 2 AS MedianScore,
    u.Reputation as UserReputation
  from
    users u   left outer join
    posts   on u.Id = posts.OwnerUserId join
    posttypes on posts.PostTypeId = posttypes.Id
  where
    posttypes.Name = 'Answer'
  group by u.Id, u.Reputation
)

select
  UserId as [User Link],
  UserReputation as [User Reputation],
  Posts as [Num Posts],
  round(MedianScore, 1) as [Median Score],
  round(AvgScore, 1) as [Average Score]
from percentages
where Posts >= 10
order by AvgScore desc;

Enter Parameters

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