2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for researchers, developers, and users of Tor

create table #Users (
  Id int,
  Reputation int,
  [Upvotes Received] int,
  [Downvotes Received] int,
  [Post Count] int
);

  insert into #Users (Id, Reputation, [Upvotes Received], [Downvotes Received], [Post Count])
  select
    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
  where (select count(*) from Posts p where p.OwnerUserId = u.Id) >= 100
    and u.Id > 0
  order by u.Reputation desc

select
  Id as [User Link],
  (10 * NULLIF([Upvotes Received], 0) / NULLIF([Downvotes Received], 0)) / 10.0 as [Upvoted:Downvoted Ratio],
  [Post Count],
  (10 * ([Upvotes Received] - [Downvotes Received]) / NULLIF([Post Count], 0))/10.0 as [Average Score],
  Reputation,
  [Upvotes Received],
  [Downvotes Received]
from #Users
order by
  1.0 * NULLIF([Upvotes Received], 0) / NULLIF([Downvotes Received], 0) asc

fork query

edited aug 30 12

created apr 16 12 Jeremy Banks