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 sum(v.BountyAmount) desc, u.Id)
,u.Id as [User Link]
,u.Reputation
,count(v.Id) as Bounties
,avg(v.BountyAmount) as [Average Spent]
,sum(v.BountyAmount) as [Total Spent]
,[Percent of Rep Spent] = format(1.0 * sum(v.BountyAmount) / nullif(sum(v.BountyAmount) + u.Reputation, 0.0), 'p3')
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
group by
 u.Id
,u.Reputation
order by
 [Total Spent] desc
,u.Id
offset ##OffsetRows:int?0## rows
fetch next ##ReturnRows:int?10## rows only;

-- OffsetRows: Number of rows to skip
-- ReturnRows: Number of rows to return

Enter Parameters

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