5

Please login or register to vote for this query.

(click on this box to dismiss)

Maybe a different cutoff value for the number of posts or a changed ordering would be preferable.

Q&A for scholars and enthusiasts of literature

-- Users with many upvoted posts, but low average score
-- Maybe a different cutoff value for the number of posts or a
-- changed ordering would be preferable.

with upvoted as (
  select
    posts.Id as PostId
  from
    posts inner join
    votes on posts.Id = votes.PostId
  where votes.VoteTypeId = 2 -- upvotes
    and posts.PostTypeId = 2 -- answers
  group by posts.Id
),

percentages as (
  select
    users.Id as UserId,
    count(posts.Id) as Posts,
    count(upvoted.PostId) as UpvotedPosts,
    count(upvoted.PostId) * 100.0 / count(posts.Id) as UpvotedPc,
    sum(posts.score) * 1.0 / count(posts.Id) as AvgScore
  from
    users   left outer join
    posts   on users.Id = posts.OwnerUserId
            left outer join
    upvoted on posts.Id = upvoted.PostId
  where
    posts.PostTypeId = 2
  group by users.Id
)

select top 1000
  UserId as [User Link],
  Posts,
  UpvotedPosts as [Upvoted Posts],
  round(UpvotedPc, 1) as [Upvoted %],
  round(AvgScore, 1) as [Average Score]
from percentages
where Posts >= 10
order by UpvotedPc - abs(AvgScore)*AvgScore desc;

fork query

created jul 14 10 sth