7

I would wish to look at past bounties that had been started on a SE site. The purpose is to gauge their "success rate" i.e. how effective have they been in pulling required answers from the community in the past. This would help me decide how large a bounty to put and whether to even put a bounty or not.

I know that once a bounty period (7 days + 24 hour grace period) has passed, it is removed from the "featured" tab. Hence, I have been unable to view the bounties that had been raised, say, in the past six months. Is such a list possible?

6

The following query does that by looking at the votes table with votetypeid = 8 (bountystart). From there it is easy to join the winning answer.

with awarded as
( 
select v.postid
     , v.bountyamount
     , v.creationdate
     , p.parentid questionid
     , p.owneruserid 
     from votes v
     inner join posts p on p.id = v.postid 
     where v.votetypeid = 9 -- award 
)

select top ##maxrows?100##
'site://q/' + cast(v.postid as nvarchar)+ '|' + q.title as [Question]
     , v.bountyamount
     , v.creationdate
     , v.userid as [User Link]
     , a.postid as [Post Link]
     , a.bountyamount
     , a.owneruserid as [User Link]
from votes v
inner join posts q on q.id = v.postid
left outer join awarded a 
  on v.postid = a.questionid 
 and a.creationdate 
 between v.creationdate 
 and dateadd(d, 8, v.creationdate)
where v.votetypeid = 8 -- bounty start
order by v.creationdate desc

When run today on Stack Overflow your result will look like this:

list of bounties

The first userlink is the user who offered the bounty, the second userlink is the OP of the answer that received the bounty. I included both bounty amounts as only 50% of the awarded bounty amount is given if the system selects the winner (instead of the user who started the bounty) in which case the second amount would then be lower.
In the votes table all creationdates are sanitized and have their time part removed so voters remain anonymous. This is maybe not needed for bounties but the rule applies to the whole table to prevent mishaps (the votes table also include up and down votes).

Keep in mind SEDE is only updated once a week, on Sunday.

Don't forget to check the awesome tutorial that learns you how to get the best out of SEDE.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .