What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 122 Stack Exchange communities.

I'm trying to construct a query to find open questions with open bounties above a particular size.

Questions that either currently have or previously had bounties can be found on data.stackexchange.com by looking for associated Votes records with a VoteTypeId of 8 (= BountyStart).

The problem is when trying to restrict these to ensure the bounty hasn't already been awarded or closed.

Can think of two possible ways:

  1. Look for questions with no associated Vote record with a VoteTypeId of 9 (= BountyClose).
  2. Bounties last for a week so restrict the BountyOpen records to those with a CreationDate under a week ago: WHERE DATEADD(d, 7, vob.CreationDate) >= GETDATE()

But neither appears to be eliminating all questions where the bounty has ended. E.g. at the time of writing, only 2 of the 5 questions returned by this query (which includes both criteria) have an open bounty.

Am I missing something? Is there another way?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

Data Explorer is not updated constantly. It's updated once a week on Mondays. A bounty lasts for 7 days, so a bounty started on a Monday after the data update wouldn't ever get shown by your query, because it would be expired by the time the data got updated again.

A better way to get this information would be using the Stack Exchange API. There's an API call for /questions/featured that returns a list in JSON format of all bountied questions. You could then parse that to sort the list by bounty size and display it.

share|improve this answer
    
Good answer but I'd like to do this all online if possible, ideally a one-click URL but if not, a bit of online copying and pasting would surfice. So am now wondering if there is any way of parsing/sorting the JSON results online... –  Steve Chambers Aug 26 '13 at 17:38
    
@SteveChambers you could probably set up a jsFiddle. You can call the API and parse JSON from JavaScript. Although if you have your own site you could just set up the script there. –  nhinkle Aug 26 '13 at 17:39
1  
Thanks, sounds a good plan. Will look into doing this... –  Steve Chambers Aug 26 '13 at 17:40
add comment

You must log in to answer this question.

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