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

declare @spentWeight float = ##spentWeight:float?1.0##;
declare @pctWeight float = ##pctWeight:float?1.5##;
declare @rankDivisor float = nullif(@spentWeight + @pctWeight, 0);

if @spentWeight < 0 begin
  raiserror('Specify a value greater than or equal to 0 for rank weight for total rep spent', 16, 1);
end

if @pctWeight < 0 begin
  raiserror('Specify a value greater than or equal to 0 for rank weight for % rep spen', 16, 1);
end

;with bounties as
(
  select
   u.Id
  ,u.Reputation
  ,v.BountyAmount
  ,case
    when q.OwnerUserId = v.UserId 
    or exists
    (
      select top 1
       1
      from Posts as a
      where a.ParentId = q.Id
      and a.OwnerUserId = v.UserId
    ) then 1
    else 0
   end as Selfish
  from Users as u
    join Votes as v
      join Posts as q
      on q.Id = v.PostId       
    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
)
,aggregates as
(
  select
   b.Id as [User Link]
  ,b.Reputation
  ,count(*) as Bounties
  ,[Spent on Self] = sum(iif(b.Selfish = 1, b.BountyAmount, 0))
  ,[Spent on Others] = sum(iif(b.Selfish = 0, b.BountyAmount, 0))
  ,[Total Spent] = sum(b.BountyAmount)
  from bounties as b
  group by
   b.Id
  ,b.Reputation
)
,percentages as
(
  select
   a.*
  ,[% Spent on Self] = 1.0 * a.[Spent on Self] / (a.[Total Spent] + a.Reputation)
  ,[% Spent on Others] = 1.0 * a.[Spent on Others] / (a.[Total Spent] + a.Reputation)
  ,[% Spent] = 1.0 * a.[Total Spent] / (a.[Total Spent] + a.Reputation)
  from aggregates as a
)
,initialRanking as
(
  select
   p.*
  ,[Spent on Others Rank] = rank() over (order by p.[Spent on Others] desc)
  ,[Total Spent Rank] = rank() over (order by p.[Total Spent] desc)
  ,[% Spent on Others Rank] = rank() over (order by p.[% Spent on Others] desc)  
  ,[% Spent Rank] = rank() over (order by p.[% Spent] desc)
  from percentages as p
)
select
 row_number() over 
 (
  order by 
   (i.[Spent on Others Rank] * @spentWeight + i.[% Spent on Others Rank] * @pctWeight) / @rankDivisor
  ,i.[User Link]
 )
,i.[User Link]
,i.Reputation
,i.Bounties
,i.[Spent on Self]
,i.[Spent on Others]
,i.[Total Spent]
,[% Spent on Self] = format(i.[% Spent on Self], 'p2')
,[% Spent on Others] = format(i.[% Spent on Others], 'p2')
,[% Spent] = format(i.[% Spent], 'p2')
,[Generosity Rank] = rank() over (order by (i.[Spent on Others Rank] * @spentWeight + i.[% Spent on Others Rank] * @pctWeight) / @rankDivisor)
,[Overall Rank] = rank() over (order by (i.[Total Spent Rank] * @spentWeight + i.[% Spent Rank] * @pctWeight) / @rankDivisor)
from initialRanking as i
order by
 [Generosity Rank]
,i.[User Link]
offset ##OffsetRows:int?0## rows
fetch next ##ReturnRows:int?10## rows only;

-- OffsetRows: Number of rows to skip
-- ReturnRows: Number of rows to return
-- spentWeight: Rank weight for total rep spent
-- pctWeight: Rank weight for % rep spent

Enter Parameters

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