Generous Bounty Investors

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select
 row_number() over 
 (
  order by
   (q.[% Spent Rank] + q.[Total Spent Rank]) / 2.0
   ,q.[User Link]
 )
,q.*
,(q.[% Spent Rank] + q.[Total Spent Rank]) / 2.0 as [Meta Rank]
from
(
  select
   u.Id as [User Link]
  ,u.Reputation
  ,count(v.Id) as Bounties
  ,sum(v.BountyAmount) as [Total Spent]
  ,[% Rep Spent] = format(1.0 * sum(v.BountyAmount) / nullif(sum(v.BountyAmount) + u.Reputation, 0.0), 'p3')
  ,[Total Spent Rank] = rank() over 
  (
   order by
    sum(v.BountyAmount) desc
  )
  ,[% Spent Rank] = rank() over 
  (
   order by
    1.0 * sum(v.BountyAmount) / nullif(sum(v.BountyAmount) + u.Reputation, 0.0) desc
  )
  from Users as u
    join Votes as v
      join Posts as p        
      on p.Id = v.PostId
      and p.OwnerUserId != v.UserId -- exclude user's questions
      and not exists
      (
        select top 1
         1
        from Posts as a
        where a.ParentId = p.Id
        and a.OwnerUserId = v.UserId -- exclude questions to which the user has provided an answer
      )
    on v.UserId = u.Id
    and v.VoteTypeId = 8 -- BountyStart
  where u.Id > 0 -- Exclude Community
  and u.Reputation > 1 -- Exclude new and suspended users
  group by
   u.Id
  ,u.Reputation
) as q
order by
 [Meta Rank]
,q.[User Link]
offset ##OffsetRows:int?0## rows
fetch next ##ReturnRows:int?10## rows only;

Enter Parameters

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