2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for active researchers, academics and students of physics

create table #Users (
  Id int,
  Reputation int,
  Upvoted int,
  Downvoted int,
  PostCount int
);

insert into #Users (Id, Reputation, Upvoted, Downvoted, PostCount)
  select top 1000
    u.Id,
    u.Reputation,
    (select count(*) from Votes v
     join Posts p on p.Id = v.PostId
     where p.OwnerUserId = u.Id and v.VoteTypeId = 2),
    (select count(*) from Votes v
     join Posts p on p.Id = v.PostId
     where p.OwnerUserId = u.Id and v.VoteTypeId = 3),
    (select count(*) from Posts p
     where p.OwnerUserId = u.Id)
  from Users u
  order by u.Reputation desc

select
  Id as [User Link],
  Reputation,
  Upvoted,
  Downvoted,
  100 * Upvoted / NULLIF(Upvoted + Downvoted, 0) as [Upvoted %],
  PostCount as [Post Count],
  (CAST(Upvoted as Float) + Downvoted) / NULLIF(PostCount, 0) as [Average Votes]
from #Users
order by Upvoted desc

view current version

edited apr 16 12

created apr 16 12 Jeremy Banks